Multiple updates in mysql?

[Enigmabomb]

How can I do multiple updates at one time with multiple where statements? Example:

Update set values (12,13,25,35,46,46,57,78,339) WHERE column = (23,224,25,26,27,28,29,30,31)

 

[GrassIsBlue]

WHERE column IN (23,224,25,26,27,28,29,30,31)

 

[silentbob]

You can but it's a bit awkard and there is a big caveat.

You could make a statement like this : (in my example I use a table called deposits with the fields : id (primary key), amount)

update deposits d1, deposits d2, deposits d3 set d1.amount=10, d2.amount=20, d3.amount=60 where d1.id=1 and d2.id=2 and d3.id=3

The d1,d2 and d3 are table aliases.

With this query all rows will be updated only if all parts of the update succeed. If for instance in the example above id 3 does not exist then the orher 2 rows will NOT be updated. Keep this in mind.

WHERE column IN (23,224,25,26,27,28,29,30,31)

This syntax will work only if you want to set the same value to all the rows.

 

[CaptainBigPants]

I take it you want to update 9 rows so that each row gets a different value? Row 23 => 12, row 224 => 13 etc.

You do need to call 9 different update statements but there's two ways you can speed up your code:

1) Make a stored procedure so there is only one call from client to database
2) Prepare the statement once with bound parameters and execute it nine times.

As silentbob says, think hard about database transactions - what should you do if one update fails.