This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using Cursors in SQL Server Part five of a five-part series of blogs
Cursors allow you to step through a set of data one record at a time. They’re not the quickest tool in SQL Server’s box, but they have their uses and this blog explains how they work. Cursors in SQL Declaring and Using Cursors in SQL Fetching Records with an SQL Cursor SQL Cursor Options Updating Records Using a Cursor (this blog) This blog is part of our complete SQL tutorial; Wise Owl also run introductory and advanced SQL training courses.

Using Cursors in SQL Server

Part five of a five-part series of blogs

Cursors allow you to step through a set of data one record at a time. They’re not the quickest tool in SQL Server’s box, but they have their uses and this blog explains how they work.

Cursors in SQL
Declaring and Using Cursors in SQL
Fetching Records with an SQL Cursor
SQL Cursor Options
Updating Records Using a Cursor (this blog)

This blog is part of our complete SQL tutorial; Wise Owl also run introductory and advanced SQL training courses.

Posted by Andrew Gould on 26 March 2013

Updating Records Using a Cursor

So far in this series we've been concerned with simply reading data from tables using a cursor. In this final part we'll look at how you can use a cursor to modify data as well.

Just to repeat the point from the start of this blog: cursors are rarely the most efficient or best way to do anything in SQL. Our SQL training includes online and classroom advanced SQL courses showing you the many set-based alternatives which are available.

Declaring a Cursor for Updating Records

The easiest way to declare a cursor that can be used to update records is to simply use the default cursor type:

DECLARE FilmCursor CURSOR

FOR SELECT FilmOscarWins FROM tblFilm

OPEN FilmCursor

Using the code above will declare a cursor which can used to update records. You can make things a little more obvious by explicitly stating that the cursor is to be used for updating, as shown below:

DECLARE FilmCursor CURSOR

FOR SELECT FilmOscarWins FROM tblFilm

FOR UPDATE

OPEN FilmCursor

This cursor can be used to update any field from the underlying data set. To make things even more specific you can list which columns from the data set you would like to make updatable, as shown below:

DECLARE FilmCursor CURSOR

FOR SELECT FilmOscarWins FROM tblFilm

FOR UPDATE OF FilmCumulativeOscars

OPEN FilmCursor

The code above ensures that only one field from the underlying data set can be updated. You can add more column names in a comma-separated list if you need to make more fields updatable.

Creating a Running Total with a Cursor

To demonstrate how to use a cursor to update records we'll create a running total of the number of Oscars our films have won. To begin with we'll declare a couple of variables and set their default values.

--Variable for the running total of Oscars

DECLARE @TotalOscars INT

--Variable for each film's Oscars

DECLARE @FilmOscars INT

SET @TotalOscars = 0

The first variable will hold the running total of Oscars while the second will be used to read each individual film's Oscars as the cursor moves through the record set.

Next, we'll declare our cursor, open it and move it to the first record. At the same time we'll read the first film's Oscar wins into the variable we declared earlier.

DECLARE FilmCursor CURSOR

FOR SELECT FilmOscarWins FROM tblFilm

FOR UPDATE OF FilmCumulativeOscars

OPEN FilmCursor

FETCH NEXT FROM FilmCursor INTO @FilmOscars

Now we can begin looping through the record set. We'll start by setting up the basic structure of the loop and adding a quick test to ensure that our variable is accumulating the number of Oscars.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TotalOscars += @FilmOscars

--This is where we'll update the records

--As a test we'll print the value of our variable

PRINT @TotalOscars

FETCH NEXT FROM FilmCursor INTO @FilmOscars

END

The result of executing the code should be a running total of the Oscars in the Messages window:

The final step is to transfer these results into the table.

To put the results we've calculated into the table we can replace our simple PRINT statement with an UPDATE statement. To make sure the correct values are added to the correct records we use the WHERE CURRENT OF statement to check the current position of our cursor. The full set of code required to make this system work is shown below:

DECLARE @TotalOscars INT

DECLARE @FilmOscars INT

SET @TotalOscars = 0

DECLARE FilmCursor CURSOR

FOR SELECT FilmOscarWins FROM tblFilm

FOR UPDATE OF FilmCumulativeOscars

OPEN FilmCursor

FETCH NEXT FROM FilmCursor INTO @FilmOscars

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TotalOscars += @FilmOscars

UPDATE tblFilm

SET FilmCumulativeOscars = @TotalOscars

WHERE CURRENT OF FilmCursor

FETCH NEXT FROM FilmCursor INTO @FilmOscars

END

CLOSE FilmCursor

DEALLOCATE FilmCursor

To see the results of this procedure we'll need to select some data from the underlying table.

SELECT

FilmName

,FilmOscarWins

,FilmCumulativeOscars

FROM

tblFilm

The results of this query are shown in the diagram below:

A fully populated column of data.

Parts of this blog
Cursors in SQL Declaring and Using Cursors in SQL Fetching Records with an SQL Cursor SQL Cursor Options Updating Records Using a Cursor (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Updating Records Using a Cursor

Declaring a Cursor for Updating Records

Creating a Running Total with a Cursor

Head office

London

Manchester