0

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.

Asim Zaidi
  • 27,016
  • 49
  • 132
  • 221

4 Answers4

3
UPDATE table1 
SET 
Order = 1 
WHERE 
ID in (2345,2837,8399)
Paul Abbott
  • 7,065
  • 3
  • 27
  • 45
2
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?

egrunin
  • 24,650
  • 8
  • 50
  • 93
1

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)
sled
  • 14,525
  • 3
  • 42
  • 70
0

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.

Zak
  • 24,947
  • 11
  • 38
  • 68
  • http://stackoverflow.com/questions/330268/i-have-an-array-of-integers-how-do-i-use-each-one-in-a-mysql-query-in-php/330280#330280 – sled Sep 13 '10 at 22:51