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;

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

---

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. 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.

---

All of the following applies to InnoDB.

I feel knowing the speeds of the 3 different methods is important.

There are 3 methods:

1. INSERT: INSERT with ON DUPLICATE KEY UPDATE
2. TRANSACTION: Where you do an update for each record within
3.  a transaction
4. CASE: In which you a case/when for each different record within 
5. an UPDATE

I just tested this, and the INSERT method was 6.7x faster for me than the

 TRANSACTION method. I tried on a set of both 3,000 and 30,000 rows.

The TRANSACTION method still has to run each individually query, which 

takes time, though it batches the results in memory, or something, while 

executing. The TRANSACTION method is also pretty expensive in both

 replication and query logs.

Even worse, the CASE method was 41.1x slower than the INSERT

 method w/ 30,000 records (6.1x slower than TRANSACTION). And 75x slower

 in MyISAM. INSERT and CASE methods broke even at ~1,000 records. 

Even at 100 records, the CASE method is BARELY faster.

So in general, I feel the INSERT method is both best and easiest to use. 

The queries are smaller and easier to read and only take up 1 query of action. 

This applies to both InnoDB and MyISAM.

Bonus stuff:

The solution for the INSERT non-default-field problem is to temporarily 

turn off the relevant SQL modes: 

SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","").

Make sure to save the sql_mode first if you plan on reverting it.

As for other comments I've seen that say the auto_increment goes up using 

the INSERT method, I tested that too and it seems to not be the case.

Code to run the tests is as follows. It also outputs .SQL files to remove php

 interpreter overhead

<?
//Variables
$NumRows=30000;


//These 2 functions need to be filled in
function InitSQL()
{
//Run the 3 tests}
function RunSQLQuery($Q)
{

}
InitSQL();
function RunTest($TestNum, $NumRows)
for($i=0;$i<3;$i++)
RunTest($i, $NumRows);


{
$DoQuery=function($Query) use (&$TheQueries)
$TheQueries=Array();
{
RunSQLQuery($Query);
$DoQuery('DROP TABLE IF EXISTS '.$TableName);
$TheQueries[]=$Query;
};
$DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
$TableName='Test';
$DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');
$DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);


if($TestNum==0) {
$TestName='Transaction';
$Start=microtime(true);
$DoQuery('START TRANSACTION');
for($i=1;$i<=$NumRows;$i++)
if($TestNum==1)
$DoQuery('COMMIT'); } {
$DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' 

ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
$TestName='Insert';
$Query=Array();
for($i=1;$i<=$NumRows;$i++)
$Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
$Start=microtime(true);
$Start=microtime(true); }
if($TestNum==2)
{
$TestName='Case';
$Query=Array();
for($i=1;$i<=$NumRows;$i++)
$Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
$DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\n

END\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
}
print "$TestName: ".(microtime(true)-$Start)."<br>\n";
}

---

Use a temporary table

// Reorder items
function update_items_tempdb(&$items)
{
shuffle($items);
$table_name = uniqid('tmp_test_');
$sql = "CREATE TEMPORARY TABLE `$table_name` ("
.", `position` int(10) unsigned NOT NULL"
." `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
.", PRIMARY KEY (`id`)"
$item->position = $i++;
.") ENGINE = MEMORY";
query($sql); $i = 0;
foreach ($items as &$item)
$sql = ''; {
query("INSERT INTO `$table_name` (id, position) VALUES $sql");
$sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})";
}
if ($sql) {
query("DROP TABLE `$table_name`");
$sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
." WHERE `$table_name`.id = `test`.id"; query($sql); }
}

---

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.

---

Why does no one mention multiple statements in one query?

In php, you use multi_query method of mysqli instance.

From the php manual

MySQL optionally allows having multiple statements in one statement string.

 Sending multiple statements at once reduces client-server round trips 

but requires special handling.

Here is the result comparing to other 3 methods in update 30,000 raw.

 Code can be found here 

Transaction: 5.5194580554962
Insert: 0.20669293403625
Case: 16.474853992462
Multi: 0.0412278175354

As you can see, multiple statements query is more efficient than the highest answer.

If you get error message like this:

PHP Warning:  Error while sending SET_OPTION packet

You may need to increase the max_allowed_packet in mysql config 

file which in my machine is /etc/mysql/my.cnf and then restart mysqld.

---

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'

---

With PHP I did this. Use semicolon, split it into array and then submit via loop.

$con = new mysqli('localhost','user1','password','my_database');
$batchUpdate = true; /*You can choose between batch and single query */
$queryIn_arr = explode(";", $queryIn);

if($batchUpdate)    /* My SQL prevents multiple insert*/
{
    foreach($queryIn_arr as $qr)
    {
        if(strlen($qr)>3)
        {
            //echo '<br>Sending data to SQL1:<br>'.$qr.'</br>';
            $result = $conn->query($qr);
        }

    }
}
else
{
    $result = $conn->query($queryIn);
}
$con->close();

---

UPDATE `your_table` SET 


`something` = IF(`id`="1","new_value1",`something`), 

`smth2` = IF(`id`="1", "nv1",`smth2`), 

`something` = IF(`id`="2","new_value2",`something`), 

`smth2` = IF(`id`="2", "nv2",`smth2`),

 `something` = IF(`id`="4","new_value3",`something`), 

`smth2` = IF(`id`="4", "nv3",`smth2`), 

`something` = IF(`id`="6","new_value4",`something`), 

`smth2` = IF(`id`="6", "nv4",`smth2`), 

`something` = IF(`id`="3","new_value5",`something`),

 `smth2` = IF(`id`="3", "nv5",`smth2`), 

`something` = IF(`id`="5","new_value6",`something`),

 `smth2` = IF(`id`="5", "nv6",`smth2`) 

// You just building it in php like

$q = 'UPDATE `your_table` SET ';

foreach($data as $dat){

  $q .= '

       `something` = IF(`id`="'.$dat->id.'","'.$dat->value.'",`something`), 
       `smth2` = IF(`id`="'.$dat->id.'", "'.$dat->value2.'",`smth2`),';

}

$q = substr($q,0,-1);

So you can update hole table with one query