Multiple Updates in MySQL

Question

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;

Answer 1

UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

This should work for ya.

There is a reference in the MySQL manual for multiple tables.

Answer 2

I'm not sure I understand. When you want to update multiple rows, do you want to update them all with the same value? in that case:

UPDATE mytable SET valueField = 'NewValue' WHERE idField IN (1,3,5,65,1584,1251)

or did you want to set multiple rows to different values?

Answer 3

The following will update all rows in one table

Update Table Set
Column1 = 'New Value'

The next one will update all rows where the value of Column2 is more than 5

Update Table Set
Column1 = 'New Value'
Where
Column2 > 5

There is all Unkwntech's example of updating more than one table

UPDATE table1, table2 SET
table1.col1 = 'value',
table2.col1 = 'value'
WHERE
table1.col3 = '567'
AND table2.col6='567'

Answer 4

You may also be interested in using joins on updates, which is possible as well.

Update someTable Set someValue = 4 From someTable s Inner Join anotherTable a on s.id = a.id Where a.id = 4
-- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4.

Edit: If the values you are updating aren't coming from somewhere else in the database, you'll need to issue multiple update queries.

Answer 5

UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

This should achieve what you'r looking for. Just add more id's. I have tested it.

Answer 6

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);

Answer 7

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 
             WHERE id IN (1, 2, 3, 4);