How does UPDATE work?
sql, sql-server, t-sql
Solution
What your colleague suggested is called Set Based approach.
Rather using while loop or processing rows one by one, you do it using Set Based approach, which is better in all aspects, let it be performance or maintenance.
As suggested you can use IN clause or Inner Join to update the table as you do have unique values to update respective table.
UPDATE table_to_update
SET whatever_you_want_to_set
From temptable
inner join temptable on temptable.key = table_to_update.key
I hope it makes sense to you.
Problem
I have been working on a stored procedure for some time now. Lately I have been wondering how exactly UPDATE works. I have a while loop where I update certain rows in a table, one by one until all relevant rows have been updated. My colleague suggested that I remove the while loop and just have one UPDATE statement instead. However, I don't know if this is possible? I don't know which rows will be updated each time the stored procedure is executed. I have table variable that holds a list of all the IDs that need to be updated and use that as a key for identifying the rows that need to be updated. Could I update all rows at the same time without using a while loop?