Update Table within CURSOR loop?
-
Can someone tell me why this wouldn't work?
What I am trying to do is take the data from one table (using a cursor) and using this data to update another table with new data. Hence the @Match and @UpdateTo variables.
If I manually set the variables for the UPDATE table withing the loop, the table will update. I have monitored the variables that the cursor fetches into, and they are correct. However, these variables then (when inserted into from the cursor) will not work in the "UPDATE tasks" portion inside the cursor.
It seems as if the UPDATE table portion won't accept the variables if they were filled by the cursor.
Thanks in advance...
DECLARE @MATCH varchar(50)
DECLARE @UpdateTo varchar(50)
DECLARE curNewList CURSOR
FAST_FORWARD
FOR
SELECT MATCH, UpdateTo From NewList --NewList is the "mapping" table
OPEN curNewList
FETCH NEXT FROM curNewList into @MATCH, @UpdateTo
While @@FETCH_STATUS = 0
begin
UPDATE tasks
Set request = @UpdateTo
From Tasks
Where request = @MATCH
FETCH NEXT FROM curNewList into @MATCH, @UpdateTo
end
close curNewList
deallocate curNewList
-
UPDATE tasks
Set request = UpdateTo
From NewList
Where request = MATCH
That's all.
No cursors.
_____________
Code for TallyGenerator -
LOL.....it's punishment for using a cursor......
-
The thing is here is that I have a column of names that I want to convert (table NewList) to a different name. I'd like to automatically loop through and change each name in the original table (Tasks).
Stating this, I am looking to take each row in the NewList, match the original name (MATCH) to the one in the Tasks table, and replace it with the name in the UpdateTo column of the NewList table.
I'm sure someone has done this somewhere, but I cannot seem to find anything that matches this scenario.
-
If my statement does not do it for you?
_____________
Code for TallyGenerator -
Sorry Sergiy, but that would only work if I replaced only one name. I have a list of names that need to be replaced. I would like to fetch OriginalName from Table2, Seach and Replace OriginalName in Table1 with NewName from Table 2.
Is there another way to loop through each name from one table and replace it with the value from another?
-
UPDATE Table1
SET Name = Table2.NewName
FROM Table2
WHERE Table2.OriginalName = Table1.Name
_____________
Code for TallyGenerator -
bwild,
if Sergiy's solution does not work for you, it is probably because you didn't explain precisely what is the situation and what you need to do. What you described so far does not require a cursor and can be done with a simple update. Please try it and if it does not produce correct results, try to be more specific about your requirements (not about HOW do you want to do it, but what is data structure and desired result).
-
Thanks all for your help, especially Sergiy for your patience.
That solution worked, and the reason I didn't think it was working correctly (leading me on a long dead end path of trying a cursor) was due to possible trailing spaces. I used LIKE instead of = in my where clause to take care of this, and got the results I needed.
I guess I tried to overcomplicate this one. This was my first use of cursors, and I've learned quite a bit about them from this experience.
-
Hi Sergiy.
Realy great solution to overcome my Time out Error.
Tnx alot.
Regards
-
Thanks Sergiy!
You are a freakin' genious. That worked perfectly.
I want to be like you when I grow up.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply