SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating through cursors


Updating through cursors

Author
Message
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 11994
Comments posted to this topic are about the item Updating through cursors


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
brewmanz
brewmanz
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 406
I used the following extract from BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7b28942f-252b-4448-88bc-dedbe53b0a82.htm (Changing Rows with Positioned Operations - Performing Positioned Updates with APIs) to kinda-guess the right answer.
"After positioning in the cursor, execute an UPDATE or DELETE statement with a WHERE CURRENT OF clause referencing the name returned by SQLGetCursorName. But this method is not recommended. Instead, it is better to use the positioned update functions in the ODBC API."
Was it a fluke, or is it some indirect help from BOL? Or maybe direct help - cursors are a bit of a *gasp* grey area for me!
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3581 Visits: 3889
I find this question a little confusing. It should be clearly stated in the question that you do not need to see updates made to the underlying data. Because that is a requirement for the suggested answer.

Best Regards,

Chris Büttner
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9872 Visits: 13350
I agree that this question was a bit confusing. I would never have chosen the "right" answer just because this is not any way to update the cursor, but the underlying data without using the cursor...

I always try to avoid using cursors and almost always succeed, but when I use cursors I always use FAST_FORWARD.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 11994
brewmanz (7/24/2008)
I used the following extract from BOL
(...)
Was it a fluke, or is it some indirect help from BOL? Or maybe direct help - cursors are a bit of a *gasp* grey area for me!


Hi Brewmanz,

That bit in Books Online is about client-side cursors, not about T-SQL cursors. When I submitted the question, it didn't occur to me to think about possible confusion with client-side cursors. That's the problem with beiing a "pure" T-SQL coder - one often tends to forget that the data is eventuallly served to and consumed by a client!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 11994
Christian Buettner (7/24/2008)
I find this question a little confusing. It should be clearly stated in the question that you do not need to see updates made to the underlying data. Because that is a requirement for the suggested answer.


Hi Christian,

I will readily admit that it had never occured to me that anyone would want to use a cursor to update data and then go back over the same data and read it again! (I already have problems realizing that there are people that use cursors on a regular basis at all!!)

However, after reading your reply I decided to go back and test it for myself, for I have never read anything to support this requirement. Here is the test code I used:

create table test (a int primary key, b int)
insert into test (a, b) select 1, 1 union all select 2, 2
go
declare @a int, @b int;
declare c cursor scroll
for select a, b from test order by a
for update of b; -- Comment this line to test alternative
open c;
fetch first from c into @a, @b;
select @a, @b;
update test set b = 10
where current of c; -- Comment this line to test alternative
--where a = @a; -- Uncomment this line to test alternative
fetch next from c into @a, @b;
select @a, @b;
fetch first from c into @a, @b;
select @a, @b;
close c;
deallocate c;
go
drop table test;
go



As you'll see if you run this code, you get the changed value back both when using FOR UPDATE and WHERE CURRENT Of, and when not using FOR UPDATE and using WHERE a = @a. All variations of cursor options I tried either showed the same result, or resulted in error messages because of illegal option combinations.

I'd apppreciate it if you can post any code that does show that changes are only visible when using FOR UPDATE.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 11994
Gianluca Sartori (7/24/2008)
I agree that this question was a bit confusing. I would never have chosen the "right" answer just because this is not any way to update the cursor, but the underlying data without using the cursor...


Hi Gianluca,

I agree that the words "need to change the data in the cursor rows" (emphasis added) can be a little confusing. With hindsight, I should have formulated this as "need to change the data retrieved by the cursor" or something similar.

But the three answer options given all do the same thing: update data in the underlying table. So even if there does exist some way to update just the cursor without updating the underlying data (which I doubt, given that a cursor is in fact a positioning mechanism and not a data colection), it should have been clear from the answer options that the intention is to change the data in the underlying table.

I always try to avoid using cursors and almost always succeed, but when I use cursors I always use FAST_FORWARD.


You might wish to check out the question for July 8, which deals with FAST_FORWARD and other options for fast (readonly) cursors.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3581 Visits: 3889
Hi Hugo,

This is a misunderstanding. What I meant to say was that when you open a static cursor, you have a point in time copy of the data as it was when you opened the cursor.
You will not see any updates made to the underlying data after that, since you are working with a copy of the data, not the stored data itself.

Best Regards,

Chris Büttner
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9872 Visits: 13350

Hugo Kornelis (7/24/2008)
[quote]Gianluca Sartori (7/24/2008)
I agree that this question was a bit confusing. I would never have chosen the "right" answer just because this is not any way to update the cursor, but the underlying data without using the cursor...


Hi Gianluca,

I agree that the words "need to change the data in the cursor rows" (emphasis added) can be a little confusing. With hindsight, I should have formulated this as "need to change the data retrieved by the cursor" or something similar.

But the three answer options given all do the same thing: update data in the underlying table. So even if there does exist some way to update just the cursor without updating the underlying data (which I doubt, given that a cursor is in fact a positioning mechanism and not a data colection), it should have been clear from the answer options that the intention is to change the data in the underlying table.


Hi Hugo, thanks for your reply!

I don't know how SQLServer handles internally the updating of the cursor using CURRENT OF, but I think it simply identifies the row to update in the underlying table from the position in cursor, without issuing additional statements.
I found myself working with an old VB6 application some years ago, using DAO to access data. DAO handles server-side cursors better than client-side cursors and I used to check for long running queries with profiler: updates to a recordset resulted in some sort of sp_cursor in profiler.
I don't know exactly what sqlserver does when you update with CURRENT OF, but I would expect something similar to this.


I always try to avoid using cursors and almost always succeed, but when I use cursors I always use FAST_FORWARD.


You might wish to check out the question for July 8, which deals with FAST_FORWARD and other options for fast (readonly) cursors.


I'm one of those who would have answered "it depends", if only the option was there.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 11994
Christian Buettner (7/24/2008)
This is a misunderstanding. What I meant to say was that when you open a static cursor, you have a point in time copy of the data as it was when you opened the cursor.
You will not see any updates made to the underlying data after that, since you are working with a copy of the data, not the stored data itself.


Hi Christian,

That's true. The options STATIC and FOR UPDATE are mutually exclusive, though.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search