Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple Updates in MySQL

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; 
like image 736
Teifion Avatar asked Aug 06 '08 14:08

Teifion


People also ask

Can you update on multiple tables in MySQL?

In MYSQL, we can update the multiple tables in a single UPDATE query. In the below query, both 'order' and 'order_detail' tables are updated at once.

How do you write multiple update statements in SQL?

We can update single columns as well as multiple columns using UPDATE statement as per our requirement. UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; table_name: name of the table column1: name of first , second, third column.... value1: new value for first, second, third column....

Can we use multiple set in update query?

No, only 1 table can be updated with an UPDATE statement.


1 Answers

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

Using your example:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12) ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2); 
like image 68
Michiel de Mare Avatar answered Oct 08 '22 13:10

Michiel de Mare