Updating a record through cursor
I have highlighted the areas which is differ from compared with a normal cursor.
When you use a Transact-SQL server cursor, you can update the current row by using an UPDATE statement that includes a WHERE CURRENT OF clause.
What I have not learned how to do is to update a single record that was read through a cursor.
SQL provides a special phrase, CURRENT OF, which you place in the WHERE clause.
You can add more column names in a comma-separated list if you need to make more fields updatable.
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.
I have highlighted the areas which differ compared with a normal cursor.
To make things even more specific you can list which columns from the data set you would like to make updatable, as shown below: The code above ensures that only one field from the underlying data set can be updated.
The words CURRENT OF, followed by the cursor name, refer to the last row that was retrieved through the cursor with the FETCH command. The set-at-a-time update to which you referred is called a searched update.
Searched updates give you a lot of bang for the buck, but positioned updates are needed when the update logic is complex.
However if you really have to use a cursor to do an update then you should mark it as insensitive: I haven't confirmed this on 2008 but certainly on 2005 and below, you can expect all kinds of weirdness if you update the data your cursor is defined against, without marking the cursor as insensitive.
So far in this series we've been concerned with simply reading data from tables using a cursor.
Thanks for the many SQL tips you and others provide in Four Hundred Guru. Maybe you can explain something I have yet to figure out.