query multiple updates
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 tha开发者_JS百科t Order may be different than 1 for those. as order field is unique.UPDATE table1
SET
Order = 1
WHERE
ID in (2345,2837,8399)
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?
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)
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.
精彩评论