"I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL?
Edit: For example I have the following
Name id Col1 Col2 Row1 1 6 1 Row2 2 2 3 Row3 3 9 5 Row4 4 16 8
I want to combine all the following Updates into one query
UPDATE table SET Col1 = 1 WHERE id = 1; UPDATE table SET Col1 = 2 WHERE id = 2; UPDATE table SET Col2 = 3 WHERE id = 3; UPDATE table SET Col1 = 10 WHERE id = 4; UPDATE table SET Col2 = 12 WHERE id = 4;"
"Since you have dynamic values, you need to use an IF or CASE for the columns to be updated. It gets kinda ugly, but it should work.
Using your example, you could do it like:
UPDATE table SET Col1 = CASE id WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 4 THEN 10 ELSE Col1 END, Col2 = CASE id WHEN 3 THEN 3 WHEN 4 THEN 12 ELSE Col2 END WHERE id IN (1, 2, 3, 4);"
"The question is old, yet I'd like to extend the topic with another answer.
My point is, the easiest way to achieve it is just to wrap multiple queries with a transaction. The accepted answer INSERT ... ON DUPLICATE KEY UPDATE is a nice hack, but one should be aware of its drawbacks and limitations:
As being said, if you happen to launch the query with rows whose primary keys don't exist in the table, the query inserts new ""half-baked"" records. Probably it's not what you want If you have a table with a not null field without default value and don't want to touch this field in the query, you'll get ""Field 'fieldname' doesn't have a default value"" MySQL warning even if you don't insert a single row at all. It will get you into trouble, if you decide to be strict and turn mysql warnings into runtime exceptions in your app.
I made some performance tests for three of suggested variants, including the INSERT ... ON DUPLICATE KEY UPDATE variant, a variant with ""case / when / then"" clause and a naive approach with transaction. You may get the python code and results here. The overall conclusion is that the variant with case statement turns out to be twice as fast as two other variants, but it's quite hard to write correct and injection-safe code for it, so I personally stick to the simplest approach: using transactions.
Edit: Findings of Dakusan prove that my performance estimations are not quite valid. Please see this answer for another, more elaborate research."
"Not sure why another useful option is not yet mentioned:
UPDATE my_table m JOIN ( SELECT 1 as id, 10 as _col1, 20 as _col2 UNION ALL SELECT 2, 5, 10 UNION ALL SELECT 3, 15, 30 ) vals ON m.id = vals.id SET col1 = _col1, col2 = _col2;"
