T-SQL: Set Operations Equivalent for cursor operation

Question

I have a table like below where I iterate using a cursor to replace one value for another. Is there a way to do this using set operations in SQL?

In this example, I would replace the column with value 2525 with the value 255 and iterate through using a cursor.

company_name_id    replacement_company_name_id
2525               255
11000201010737     10000701010293
12000301010533     12000301010532

Here's the code I am running:

declare @company_name_id bigint, @replacement_company_name_id bigint
declare company_name_cursor cursor for
select
    company_name_id
    , replacement_company_name_id
from #replacements

open company_name_cursor
fetch next from company_name_cursor into @company_name_id, @replacement_company_name_id

while @@FETCH_STATUS <> -1
begin
    update user_job_during_school_job
    set company_name_id = @replacement_company_name_id
    where company_name_id = @company_name_id

    fetch next from company_name_cursor into @company_name_id, @replacement_company_name_id
end
close company_name_cursor
deallocate company_name_cursor

score 1 · Accepted Answer · answered Apr 27 '13 at 08:25

1

Try this one -

UPDATE uj
SET company_name_id = r.replacement_company_name_id
FROM dbo.user_job_during_school_job uj
JOIN #replacements r ON uj.company_name_id = r.company_name_id

answered Apr 27 '13 at 08:25

Devart

119,203
23
166
186

score 0 · Answer 2 · answered Apr 27 '13 at 03:41

I think you need to show your code for cursor so we have idea of what you are doing. If it's just to replace one column with another, an UPDATE statement could do that

UPDATE myTable SET
   company_name_id = replacement_company_name_id

And if restricting the value 2525, then add a WHERE clause

UPDATE myTable SET
   company_name_id = replacement_company_name_id
WHERE
   company_name_id = 2525

T-SQL: Set Operations Equivalent for cursor operation

2 Answers2