query multiple updates

Question

how do I do multiple updates within same query.

Lets say I have this query

UPDATE table1
SET
Order = 1
WHERE
ID = 1234  

But I want to change some more orders where IDs are 2345 and 2837 and 8399
how would I do that within same mysql query. Please notice that Order may be different than 1 for those. as order field is unique.

Answer 1

UPDATE table1
SET Order = 1
WHERE id IN (1234, 2345, 2837, 8399)

If you need different values of Order for each id, then you probably want to put a loop in whatever program is talking to the database.

Edited to add

I was thinking of looping in a program, but I see you want to execute the SQL interactively.

If you're trying to bundle a sequence of unpredictable numbers like this:

UPDATE table1  Order = 1 WHERE id = 1234
UPDATE table1  Order = 2 WHERE id = 2345
UPDATE table1  Order = 3 WHERE id = 2837
UPDATE table1  Order = 5 WHERE id = 8399
UPDATE table1  Order = 8 WHERE id = 8675

...then I'm not sure how you want that to be shorter. You can write a stored procedure that gets called like this:

do_table1_update "1,2,3,5,8,13", "1234,2345,2837,8399,8675,309"

Is that what you have in mind?

Answer 2

UPDATE table1 
SET 
Order = 1 
WHERE 
ID in (2345,2837,8399)

Answer 3

Hi,

you can update multiple rows but you can't set the order field to different values, so all order fields (with matching ID) will have the value 1

UPDATE table1 SET Order = 1 WHERE ID IN(1234, 2345, 2837, 8399)

Answer 4

If you want to update the other orders with the same ID of 1, use the IN statement as others have posted. If you don't, use PDO, and a parameterized query.