开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜