CODEWITHSUNDEEP
sqlsql-servert-sql
hermann
hermann

Reputation: 6295

How does UPDATE work?

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?

Upvotes: 3

Views: 4654

Answers (4)

Jess
Jess

Reputation: 25099

Yes.

One thing I've done before is use a join with my update. I know it sounds weird, but it worked.

You could also build an in clause or an exists sub select.

One of the dangers of an in clause is that the list of values you use is finite. I have run into an issue in the past where my SQL statement got too long and failed to execute.

Upvotes: 1

Nilesh Thakkar
Nilesh Thakkar

Reputation: 2895

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.

Upvotes: 4

user506170
user506170

Reputation:

Your colleague is right, because multiple queries could eventually slow your database down. Big queries are less of a problem. I suggest you follow the instructions in this post, which demonstrates the power of the UPDATE command quite well: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/.

Of course, making up a single query in PHP makes it more likely that you will make a mistake. I recommend that you just have PHP output your query, than you can check the whole thing straight into MySQL (or whatever else you use). Of course, don't use the live database for this ;)

Upvotes: 2

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10512

Yes, you can. There are different ways to do that, one of them is

UPDATE table_to_update 
    SET whatever_you_want_to_set 
    WHERE table_id IN (SELECT id FROM @tableVariable);

And your colleague has the point - single big UPDATE statement in general executes much faster than separate small UPDATEs.

Upvotes: 1

Related Questions