views:

3717

answers:

7

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;
A: 
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.

Unkwntech
+1  A: 

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?

Michael Stum
+1  A: 

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'
GateKiller
+1  A: 

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.

Shawn Simon
A: 
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.

Unkwntech
+12  A: 

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);
Michiel de Mare
If there is no duplicates then i dont want that row to be inserted. what should id do? because i am fetching information from another site which maintains tables with id's. I am inserting values with respect to that id. if the site has new records then i will end up inserting only the ids and count except all other information. if and only if there is an entry for the id then it should update else it should skip. what shall i do?
Jayapal Chandran
so if it is like insert into skip null or skip empty on duplicate update ... then that would be nice.
Jayapal Chandran
+1  A: 

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