<p>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?</p>

<p>Edit:
For example I have the following</p>

<pre><code>Name   id  Col1  Col2
Row1   1    6     1
Row2   2    2     3
Row3   3    9     5
Row4   4    16    8
</code></pre>

<p>I want to combine all the following Updates into one query</p>

<pre><code>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;
</code></pre>

[{'answer_id': 3439, 'author': 'UnkwnTech', 'author_id': 115, 'author_profile': 'https://Stackoverflow.com/users/115', 'pm_score': 3, 'selected': False, 'text': '<pre><code>UPDATE table1, table2 SET table1.col1=\'value\', table2.col1=\'value\' WHERE table1.col3=\'567\' AND table2.col6=\'567\'\n</code></pre>\n\n<p>This should work for ya.</p>\n\n<p>There is a reference in <a href="http://dev.mysql.com/doc/refman/5.0/en/update.html" rel="noreferrer">the MySQL manual</a> for multiple tables.</p>\n'}, {'answer_id': 3444, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': -1, 'selected': False, 'text': '<p>The following will update all rows in one table</p>\n\n<pre><code>Update Table Set\nColumn1 = \'New Value\'\n</code></pre>\n\n<p>The next one will update all rows where the value of Column2 is more than 5</p>\n\n<pre><code>Update Table Set\nColumn1 = \'New Value\'\nWhere\nColumn2 &gt; 5\n</code></pre>\n\n<p>There is all <a href="https://stackoverflow.com/questions/3432/multiple-updates-in-mysql#3439">Unkwntech</a>\'s example of updating more than one table</p>\n\n<pre><code>UPDATE table1, table2 SET\ntable1.col1 = \'value\',\ntable2.col1 = \'value\'\nWHERE\ntable1.col3 = \'567\'\nAND table2.col6=\'567\'\n</code></pre>\n'}, {'answer_id': 3445, 'author': 'Shawn', 'author_id': 26, 'author_profile': 'https://Stackoverflow.com/users/26', 'pm_score': 2, 'selected': False, 'text': "<p>You may also be interested in using joins on updates, which is possible as well.</p>\n\n<pre><code>Update someTable Set someValue = 4 From someTable s Inner Join anotherTable a on s.id = a.id Where a.id = 4\n-- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4.\n</code></pre>\n\n<p>Edit: If the values you are updating aren't coming from somewhere else in the database, you'll need to issue multiple update queries.</p>\n"}, {'answer_id': 3449, 'author': 'UnkwnTech', 'author_id': 115, 'author_profile': 'https://Stackoverflow.com/users/115', 'pm_score': -1, 'selected': False, 'text': "<pre><code>UPDATE tableName SET col1='000' WHERE id='3' OR id='5'\n</code></pre>\n\n<p>This should achieve what you'r looking for.  Just add more id's.  I have tested it.</p>\n"}, {'answer_id': 3466, 'author': 'Michiel de Mare', 'author_id': 136, 'author_profile': 'https://Stackoverflow.com/users/136', 'pm_score': 10, 'selected': True, 'text': "<p>Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.</p>\n\n<p>Using your example:</p>\n\n<pre><code>INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)\nON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);\n</code></pre>\n"}, {'answer_id': 84111, 'author': 'Harrison Fisk', 'author_id': 16111, 'author_profile': 'https://Stackoverflow.com/users/16111', 'pm_score': 7, 'selected': False, 'text': '<p>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.</p>\n\n<p>Using your example, you could do it like:</p>\n\n<pre>\nUPDATE table SET Col1 = CASE id \n                          WHEN 1 THEN 1 \n                          WHEN 2 THEN 2 \n                          WHEN 4 THEN 10 \n                          ELSE Col1 \n                        END, \n                 Col2 = CASE id \n                          WHEN 3 THEN 3 \n                          WHEN 4 THEN 12 \n                          ELSE Col2 \n                        END\n             WHERE id IN (1, 2, 3, 4);\n</pre>\n'}, {'answer_id': 5213557, 'author': 'Brooks', 'author_id': 126001, 'author_profile': 'https://Stackoverflow.com/users/126001', 'pm_score': 2, 'selected': False, 'text': '<p>There is a setting you can alter called \'multi statement\' that disables MySQL\'s \'safety mechanism\' implemented to prevent (more than one) injection command. Typical to MySQL\'s \'brilliant\' implementation, it also prevents user from doing efficient queries.</p>\n\n<p>Here (<a href="http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html</a>) is some info on the C implementation of the setting.</p>\n\n<p>If you\'re using PHP, you can use mysqli to do multi statements (I think php has shipped with mysqli for a while now)</p>\n\n<pre><code>$con = new mysqli(\'localhost\',\'user1\',\'password\',\'my_database\');\n$query = "Update MyTable SET col1=\'some value\' WHERE id=1 LIMIT 1;";\n$query .= "UPDATE MyTable SET col1=\'other value\' WHERE id=2 LIMIT 1;";\n//etc\n$con-&gt;multi_query($query);\n$con-&gt;close();\n</code></pre>\n\n<p>Hope that helps.</p>\n'}, {'answer_id': 5577503, 'author': 'Laymain', 'author_id': 696291, 'author_profile': 'https://Stackoverflow.com/users/696291', 'pm_score': 3, 'selected': False, 'text': '<p>Use a temporary table</p>\n<pre class="lang-php prettyprint-override"><code>// Reorder items\nfunction update_items_tempdb(&amp;$items)\n{\n    shuffle($items);\n    $table_name = uniqid(\'tmp_test_\');\n    $sql = &quot;CREATE TEMPORARY TABLE `$table_name` (&quot;\n        .&quot;  `id` int(10) unsigned NOT NULL AUTO_INCREMENT&quot;\n        .&quot;, `position` int(10) unsigned NOT NULL&quot;\n        .&quot;, PRIMARY KEY (`id`)&quot;\n        .&quot;) ENGINE = MEMORY&quot;;\n    query($sql);\n    $i = 0;\n    $sql = \'\';\n    foreach ($items as &amp;$item)\n    {\n        $item-&gt;position = $i++;\n        $sql .= ($sql ? \', \' : \'\').&quot;({$item-&gt;id}, {$item-&gt;position})&quot;;\n    }\n    if ($sql)\n    {\n        query(&quot;INSERT INTO `$table_name` (id, position) VALUES $sql&quot;);\n        $sql = &quot;UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position&quot;\n            .&quot; WHERE `$table_name`.id = `test`.id&quot;;\n        query($sql);\n    }\n    query(&quot;DROP TABLE `$table_name`&quot;);\n}\n</code></pre>\n'}, {'answer_id': 14128210, 'author': 'eggmatters', 'author_id': 1010444, 'author_profile': 'https://Stackoverflow.com/users/1010444', 'pm_score': 2, 'selected': False, 'text': '<p>You can alias the same table to give you the id\'s you want to insert by (if you are doing a row-by-row update:</p>\n\n<pre><code>UPDATE table1 tab1, table1 tab2 -- alias references the same table\nSET \ncol1 = 1\n,col2 = 2\n. . . \nWHERE \ntab1.id = tab2.id;\n</code></pre>\n\n<p>Additionally, It should seem obvious that you can also update from other tables as well. In this case, the update doubles as a "SELECT" statement, giving you the data from the table you are specifying. You are explicitly stating in your query the update values so, the second table is unaffected.</p>\n'}, {'answer_id': 17284265, 'author': 'Roman Imankulov', 'author_id': 848010, 'author_profile': 'https://Stackoverflow.com/users/848010', 'pm_score': 7, 'selected': False, 'text': '<p>The question is old, yet I\'d like to extend the topic with another answer.</p>\n\n<p>My point is, the easiest way to achieve it is just to wrap multiple queries with a transaction. The accepted answer <code>INSERT ... ON DUPLICATE KEY UPDATE</code> is a nice hack, but one should be aware of its drawbacks and limitations:</p>\n\n<ul>\n<li>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</li>\n<li>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 <code>"Field \'fieldname\' doesn\'t have a default value"</code> 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.</li>\n</ul>\n\n<p>I made some performance tests for three of suggested variants, including the <code>INSERT ... ON DUPLICATE KEY UPDATE</code> variant, a variant with "case / when / then" clause and a naive approach with transaction. You may get the python code and results <a href="https://gist.github.com/imankulov/5849790" rel="noreferrer">here</a>. 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.</p>\n\n<p><strong>Edit:</strong> Findings of <a href="https://stackoverflow.com/users/698632/dakusan">Dakusan</a> prove that my performance estimations are not quite valid. Please see <a href="https://stackoverflow.com/a/39831043/848010">this answer</a> for another, more elaborate research.</p>\n'}, {'answer_id': 18492422, 'author': 'user2082581', 'author_id': 2082581, 'author_profile': 'https://Stackoverflow.com/users/2082581', 'pm_score': -1, 'selected': False, 'text': '<pre><code>UPDATE `your_table` SET \n\n`something` = IF(`id`="1","new_value1",`something`), `smth2` = IF(`id`="1", "nv1",`smth2`),\n`something` = IF(`id`="2","new_value2",`something`), `smth2` = IF(`id`="2", "nv2",`smth2`),\n`something` = IF(`id`="4","new_value3",`something`), `smth2` = IF(`id`="4", "nv3",`smth2`),\n`something` = IF(`id`="6","new_value4",`something`), `smth2` = IF(`id`="6", "nv4",`smth2`),\n`something` = IF(`id`="3","new_value5",`something`), `smth2` = IF(`id`="3", "nv5",`smth2`),\n`something` = IF(`id`="5","new_value6",`something`), `smth2` = IF(`id`="5", "nv6",`smth2`) \n</code></pre>\n\n<p>//  You just building it in php like</p>\n\n<pre><code>$q = \'UPDATE `your_table` SET \';\n\nforeach($data as $dat){\n\n  $q .= \'\n\n       `something` = IF(`id`="\'.$dat-&gt;id.\'","\'.$dat-&gt;value.\'",`something`), \n       `smth2` = IF(`id`="\'.$dat-&gt;id.\'", "\'.$dat-&gt;value2.\'",`smth2`),\';\n\n}\n\n$q = substr($q,0,-1);\n</code></pre>\n\n<p>So you can update hole table with one query</p>\n'}, {'answer_id': 19033152, 'author': 'newtover', 'author_id': 68998, 'author_profile': 'https://Stackoverflow.com/users/68998', 'pm_score': 6, 'selected': False, 'text': '<p>Not sure why another useful option is not yet mentioned:</p>\n\n<pre><code>UPDATE my_table m\nJOIN (\n    SELECT 1 as id, 10 as _col1, 20 as _col2\n    UNION ALL\n    SELECT 2, 5, 10\n    UNION ALL\n    SELECT 3, 15, 30\n) vals ON m.id = vals.id\nSET col1 = _col1, col2 = _col2;\n</code></pre>\n'}, {'answer_id': 25217509, 'author': 'sara191186', 'author_id': 2144822, 'author_profile': 'https://Stackoverflow.com/users/2144822', 'pm_score': 0, 'selected': False, 'text': '<p>Yes ..it is possible using INSERT ON DUPLICATE KEY UPDATE sql statement..\nsyntax:\nINSERT INTO table_name (a,b,c) VALUES (1,2,3),(4,5,6)\n    ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c)</p>\n'}, {'answer_id': 36017552, 'author': 'Justin Levene', 'author_id': 1938802, 'author_profile': 'https://Stackoverflow.com/users/1938802', 'pm_score': 0, 'selected': False, 'text': '<p>use</p>\n\n<pre><code>REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES\n(1,6,1),(2,2,3),(3,9,5),(4,16,8);\n</code></pre>\n\n<p>Please note:</p>\n\n<ul>\n<li>id has to be a primary unique key </li>\n<li>if you use foreign keys to\nreference the table, REPLACE deletes then inserts, so this might\ncause an error</li>\n</ul>\n'}, {'answer_id': 39831043, 'author': 'Dakusan', 'author_id': 698632, 'author_profile': 'https://Stackoverflow.com/users/698632', 'pm_score': 6, 'selected': False, 'text': '<p>All of the following applies to InnoDB.</p>\n<p>I feel knowing the speeds of the 3 different methods is important.</p>\n<p>There are 3 methods:</p>\n<ol>\n<li>INSERT: INSERT with ON DUPLICATE KEY UPDATE</li>\n<li>TRANSACTION: Where you do an update for each record within a transaction</li>\n<li>CASE: In which you a case/when for each different record within an UPDATE</li>\n</ol>\n<p>I just tested this, and the INSERT method was <strong>6.7x</strong> faster for me than the TRANSACTION method. I tried on a set of both 3,000 and 30,000 rows.</p>\n<p>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.</p>\n<p>Even worse, the CASE method was <strong>41.1x</strong> slower than the INSERT method w/ 30,000 records (6.1x slower than TRANSACTION). And <strong>75x</strong> slower in MyISAM. INSERT and CASE methods broke even at ~1,000 records. Even at 100 records, the CASE method is BARELY faster.</p>\n<p>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.</p>\n<p><strong>Bonus stuff:</strong></p>\n<p>The solution for the INSERT non-default-field problem is to temporarily turn off the relevant SQL modes: <code>SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,&quot;STRICT_TRANS_TABLES&quot;,&quot;&quot;),&quot;STRICT_ALL_TABLES&quot;,&quot;&quot;)</code>. Make sure to save the <code>sql_mode</code> first if you plan on reverting it.</p>\n<p>As for other comments I\'ve seen that say the auto_increment goes up using the INSERT method, this does seem to be the case in InnoDB, but not MyISAM.</p>\n<p>Code to run the tests is as follows. It also outputs .SQL files to remove php interpreter overhead</p>\n<pre class="lang-php prettyprint-override"><code>&lt;?php\n//Variables\n$NumRows=30000;\n\n//These 2 functions need to be filled in\nfunction InitSQL()\n{\n\n}\nfunction RunSQLQuery($Q)\n{\n\n}\n\n//Run the 3 tests\nInitSQL();\nfor($i=0;$i&lt;3;$i++)\n    RunTest($i, $NumRows);\n\nfunction RunTest($TestNum, $NumRows)\n{\n    $TheQueries=Array();\n    $DoQuery=function($Query) use (&amp;$TheQueries)\n    {\n        RunSQLQuery($Query);\n        $TheQueries[]=$Query;\n    };\n\n    $TableName=\'Test\';\n    $DoQuery(\'DROP TABLE IF EXISTS \'.$TableName);\n    $DoQuery(\'CREATE TABLE \'.$TableName.\' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB\');\n    $DoQuery(\'INSERT INTO \'.$TableName.\' (i2) VALUES (\'.implode(\'), (\', range(2, $NumRows+1)).\')\');\n\n    if($TestNum==0)\n    {\n        $TestName=\'Transaction\';\n        $Start=microtime(true);\n        $DoQuery(\'START TRANSACTION\');\n        for($i=1;$i&lt;=$NumRows;$i++)\n            $DoQuery(\'UPDATE \'.$TableName.\' SET i2=\'.(($i+5)*1000).\' WHERE i1=\'.$i);\n        $DoQuery(\'COMMIT\');\n    }\n    \n    if($TestNum==1)\n    {\n        $TestName=\'Insert\';\n        $Query=Array();\n        for($i=1;$i&lt;=$NumRows;$i++)\n            $Query[]=sprintf(&quot;(%d,%d)&quot;, $i, (($i+5)*1000));\n        $Start=microtime(true);\n        $DoQuery(\'INSERT INTO \'.$TableName.\' VALUES \'.implode(\', \', $Query).\' ON DUPLICATE KEY UPDATE i2=VALUES(i2)\');\n    }\n    \n    if($TestNum==2)\n    {\n        $TestName=\'Case\';\n        $Query=Array();\n        for($i=1;$i&lt;=$NumRows;$i++)\n            $Query[]=sprintf(\'WHEN %d THEN %d\', $i, (($i+5)*1000));\n        $Start=microtime(true);\n        $DoQuery(&quot;UPDATE $TableName SET i2=CASE i1\\n&quot;.implode(&quot;\\n&quot;, $Query).&quot;\\nEND\\nWHERE i1 IN (&quot;.implode(\',\', range(1, $NumRows)).\')\');\n    }\n    \n    print &quot;$TestName: &quot;.(microtime(true)-$Start).&quot;&lt;br&gt;\\n&quot;;\n\n    file_put_contents(&quot;./$TestName.sql&quot;, implode(&quot;;\\n&quot;, $TheQueries).\';\');\n}\n</code></pre>\n'}, {'answer_id': 44931466, 'author': 'mononoke', 'author_id': 6088837, 'author_profile': 'https://Stackoverflow.com/users/6088837', 'pm_score': 3, 'selected': False, 'text': '<p>Why does no one mention <strong>multiple statements in one query</strong>?</p>\n\n<p>In php, you use <code>multi_query</code> method of mysqli instance.</p>\n\n<p>From the <a href="http://php.net/manual/en/mysqli.quickstart.multiple-statement.php" rel="noreferrer">php manual</a></p>\n\n<blockquote>\n  <p>MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.</p>\n</blockquote>\n\n<p>Here is the result comparing to other 3 methods in update 30,000 raw. Code can be found <a href="https://github.com/yanxurui/keepcoding/blob/master/php/mysql/multiple_update.php" rel="noreferrer">here</a> which is based on answer from @Dakusan </p>\n\n<p><strong>Transaction: 5.5194580554962<br>\nInsert: 0.20669293403625<br>\nCase: 16.474853992462<br>\nMulti: 0.0412278175354<br></strong></p>\n\n<p>As you can see, multiple statements query is more efficient than the highest answer.</p>\n\n<p>If you get error message like this:</p>\n\n<pre><code>PHP Warning:  Error while sending SET_OPTION packet\n</code></pre>\n\n<p>You may need to increase the <code>max_allowed_packet</code> in mysql config file which in my machine is <code>/etc/mysql/my.cnf</code> and then restart mysqld.</p>\n'}, {'answer_id': 61643990, 'author': 'Stan Sokolov', 'author_id': 1610778, 'author_profile': 'https://Stackoverflow.com/users/1610778', 'pm_score': 1, 'selected': False, 'text': '<p>And now the easy way</p>\n<pre><code>update my_table m, -- let create a temp table with populated values\n    (select 1 as id, 20 as value union -- this part will be generated\n     select 2 as id, 30 as value union -- using a backend code\n     -- for loop \n     select N as id, X as value\n        ) t\nset m.value = t.value where t.id=m.id -- now update by join - quick\n</code></pre>\n'}, {'answer_id': 65950733, 'author': 'Liam', 'author_id': 3714181, 'author_profile': 'https://Stackoverflow.com/users/3714181', 'pm_score': 0, 'selected': False, 'text': "<p>I took the answer from @newtover and extended it using the new json_table function in MySql 8. This allows you to create a stored procedure to handle the workload rather than building your own SQL text in code:</p>\n<pre><code>drop table if exists `test`;\ncreate table `test` (\n  `Id` int,\n  `Number` int,\n  PRIMARY KEY (`Id`)\n);\ninsert into test (Id, Number) values (1, 1), (2, 2);\n\nDROP procedure IF EXISTS `Test`;\nDELIMITER $$\nCREATE PROCEDURE `Test`(\n    p_json json\n)\nBEGIN\n    update test s\n        join json_table(p_json, '$[*]' columns(`id` int path '$.id', `number` int path '$.number')) v \n        on s.Id=v.id set s.Number=v.number;\nEND$$\nDELIMITER ;\n\ncall `Test`('[{&quot;id&quot;: 1, &quot;number&quot;: 10}, {&quot;id&quot;: 2, &quot;number&quot;: 20}]');\nselect * from test;\n\ndrop table if exists `test`;\n</code></pre>\n<p>It's a few ms slower than pure SQL but I'm happy to take the hit rather than generate the sql text in code. Not sure how performant it is with huge recordsets (the JSON object has a max size of 1Gb) but I use it all the time when updating 10k rows at a time.</p>\n"}]
['https://Stackoverflow.com/questions/3432', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1384652/']