Update using WHILE and variables

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 2000 Forums

Transact-SQL (2000)

Update using WHILE and variables

Author

Topic

corey
Starting Member

13 Posts

Posted - 2004-11-30 : 14:04:50

I can't seem to get this to run correctly. The while statement causes it to hang. If I exclude the WHILE I am allowed to update the top record but ideally it would be nice to run through all the records meeting this criteria and update them all at the same time.

Can someone help?

BEGIN
declare @total as int
declare @counter as int
declare @pk_id as varchar(10)
SET @counter = 0
SET @total = (SELECT count(f.dup_id)
FROM sysadm.fixdup f
WHERE f.result = '10' and left(f.dup_id,1) = 'A')
WHILE @counter < @total
SET @pk_id = (SELECT TOP 1 (f1.dup_id)
FROM sysadm.fixdup f1
WHERE f1.result = '10' and left(f1.dup_id,1) = 'A'
ORDER BY f1.update_stamp)

/* Insert a delete statement here for another table OR insert an update statement to update the correct pk and setting
sequence field = max of sequence + 1 */

UPDATE sysadm.fixdup
SET result = NULL
WHERE dup_id = @animal_id
SET @counter = @counter + 1
END

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-30 : 14:12:49

You are only executing the update statement in the loop - as you don't update the counter then it just carries on forever updating the same rec.

BEGIN
declare @total as int
declare @counter as int
declare @pk_id as varchar(10)
SET @counter = 0
SET @total = (SELECT count(f.dup_id)
FROM sysadm.fixdup f
WHERE f.result = '10' and left(f.dup_id,1) = 'A')
WHILE @counter < @total
begin
SET @pk_id = (SELECT TOP 1 (f1.dup_id)
FROM sysadm.fixdup f1
WHERE f1.result = '10' and left(f1.dup_id,1) = 'A'
ORDER BY f1.update_stamp)

/* Insert a delete statement here for another table OR insert an update statement to update the correct pk and setting
sequence field = max of sequence + 1 */

UPDATE sysadm.fixdup
SET result = NULL
WHERE dup_id = @animal_id
SET @counter = @counter + 1
end
END

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-30 : 14:15:31

But couldn't you d this in a single update statement?
And what's @animal_id?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

corey
Starting Member

13 Posts

Posted - 2004-11-30 : 14:16:46

Thank you so much nr...I knew I was overlooking something. Works great!

corey
Starting Member

13 Posts

Posted - 2004-11-30 : 14:19:47

I guess I could but I am looking at updating or deleting as well. Maybe I am making this harder than it needs to be but I couldn't think of another route.

@animal_id was a variable I was using and forgot to remove, it is basically @pk_id. Sorry to confuse you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources