Cursor and Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor and Update

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-06 : 09:29:19

i am trying to update each row that the cursor fetch,but it seem's nothing happend.
this is the code :



Declare  @i int
set @i=1
DECLARE c1 CURSOR FOR
SELECT TOP 10 content 
FROM Messages0812  Where txt<>''
FOR UPDATE OF content

OPEN c1

WHILE @@FETCH_STATUS = 0 AND @i<=10
BEGIN
	print @i
	UPDATE Messages0812 
	SET content = '' WHERE CURRENT OF c1
	set @i=@i+1
End
CLOSE c1
DEALLOCATE c1

when i check the @@FETCH_STATUS i get it's value is -1,which mean's it dosent enter the While loop at all!
any idea?
Thanks in advance
Peleg

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-06 : 09:54:14

2 things :
1)missing: OPEN c1 FETCH NEXT FROM c1
20can't make a cursor on a VIEW

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 14:06:33

I don't see a reason you need a cursor. PLease post some sample data and desired results and someone will help you w/o a cursor. In sql it is really never prefferable to use a loop unless that is the last option, and the only case where it tends to be the last option is if you are required when updating a table is when you want to run a stored procedure for each result returned.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-06 : 15:37:30

i have millions of rows
where the key is the date
and i want update every 10000 rows ,and change the value of that column to '' (empty string)
any better id then cursor? (i dont have an id column!)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 16:05:11

Yes


Update a
set a.mycol = ''
from
(Select Row_Number() over (Order by (Select 1)) as RowID,*
from mytable) a
where RowID %10000 = 0

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-06 : 17:12:57

first thnaks!
can you explain this part fo the code :
over (Order by (Select 1))

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 19:40:23

Yes in order to use The Row_Number() function it MUST contain a ORDER BY so sql knows what record to # first. The (Select 1) is basically a way to trick sql into using the record order rather than any specific column, so basically the order of your records will coinside with the row number/ID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-07 : 03:34:58

ok Thnaks alot!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-07 : 05:16:03

by the way
is there a dirffrence if i will put instead of :
where RowID %10000 =
i will do :
Select Top 10000 Row_Number() over........

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources