Sequential updates

[Gymnast]

I am trying to update records in a table in sequence by setting up a Cursor and using Fetch like so.. DECLARE VPres_Cursor CURSOR FOR select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident ON dbo.Person.PersonID = dbo.VicePresident.PersonID OPEN VPres_Cursor; FETCH NEXT FROM VPres_Cursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM VPres_Cursor; update dbo.VicePresident set dbo.VicePresident.LastName = LastName END; CLOSE VPres_Cursor; DEALLOCATE VPres_Cursor; While I am able to see the records in the Results window in SQL Management Studio, I am not able to see the actual update in the table. 47 Burr 48 Clinton ... .. 81 Humphrey Can anyone help?? Thanks in advance.. Gymnast.

[Niladri_Biswas]

Do like this

DECLARE @LASTNAME VARCHAR(50)

DECLARE VPres_Cursor CURSOR FOR select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident ON dbo.Person.PersonID = dbo.VicePresident.PersonID OPEN VPres_Cursor;

FETCH VPres_Cursor INTO @LASTNAME

WHILE @@FETCH_STATUS = 0 BEGIN

update dbo.VicePresident
set dbo.VicePresident.LastName = @LASTNAME

    FETCH VPres\_Cursor INTO @LASTNAME

END; CLOSE VPres_Cursor; DEALLOCATE VPres_Cursor; I have only highlighted those lines which I think to be changed. And it is working fine in my system. Hope this helps. Let me know in case of any concern :)

Niladri Biswas

modified on Thursday, July 2, 2009 2:45 AM

[Mycroft Holmes]

Using the update in a cursor I thought you needed Where Current of cursorname otherwise you set all the names to @Lastname

Never underestimate the power of human stupidity RAH

[Niladri_Biswas]

Hello Mycroft, Actually I was not able to fathom your statement. Did you mean that, the update will be based on some condition! But actually, in the original post, the author of the code didn't specify any such thing by which I can use the Where Current of cursorname. So I thought that I should do that in the way I did!. You are always welcome to give a better solution so that I can also improve myself. :)

Niladri Biswas

[Mycroft Holmes]

When you do an update on a table if you do not apply a filter you will update every name to the variable. This updates the current record being accessed by the cursor.

Declare csrPV Cursor For
SELECT ColName_0
FROM stg.IRsCap
WHERE ID > @HeadRow
OPEN csrPV
FETCH NEXT FROM csrPV INTO @Value

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Value != ''
SET @LastValue = @Value

IF @Value = ''
BEGIN
	**UPDATE STG.IRsCap SET ColName\_0 = @LastValue
	WHERE CURRENT OF csrPV**
END	
FETCH NEXT FROM csrPV INTO @Value

END
CLOSE csrPV
DEALLOCATE csrPV

Never underestimate the power of human stupidity RAH

[Niladri_Biswas]

:)

Niladri Biswas

[Gymnast]

Thanks Mycroft! But I am yet to understand portions of the code you suggested:

SELECT ColName_0
FROM stg.IRsCap

I have a resultSet from a join that I would liek to use.. I assume I can still use that.

WHERE ID > @HeadRow

I assume that @HEADERROW is the value of the first IDin the table I am updating (which is 33 in my case).

IF @Value != ''
SET @LastValue = @Value

Here I assume that @Value is declared to hold the Value of the LastName Your input is appreciated. Thanks, Rosh