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. You may get the python code and results. 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 prove that my performance estimations are not quite valid.

Updating Multiple Rows IN One Statement. Is there any way to update several values in one SQL statement. Be faster than running multiple update. DBIx::MultiRow – Updating multiple database rows. So we still have one UPDATE statement for every row requiring. Updating multiple database rows quickly and.

Please see for another, more elaborate research. 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. Here () is some info on the C implementation of the setting. Kiran Font Marathi.

If you're using PHP, you can use mysqli to do multi statements (I think php has shipped with mysqli for a while now) $con = new mysqli('localhost','user1','password','my_database'); $query = 'Update MyTable SET col1='some value' WHERE id=1 LIMIT 1;'; $query.= 'UPDATE MyTable SET col1='other value' WHERE id=2 LIMIT 1;'; //etc $con->multi_query($query); $con->close(); Hope that helps. 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: UPDATE table1 tab1, table1 tab2 -- alias references the same table SET col1 = 1,col2 = 2...

WHERE tab1.id = tab2.id; 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. Why does no one mention multiple statements in one query? In php, you use multi_query method of mysqli instance. From the 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 which is based on answer from @Dakusan Transaction: 5.962 Insert: 0.3625 Case: 92462 Multi: 0.354 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. All below comparisons are ran against the INSERT test. I just ran the test in the same conditions and, without transactions, it was 145x slower on 300 rows and 753x slower for 3000 rows. I had originally started with the 30,000 rows, but I went to make myself lunch and came back and it was still going. This makes sense as running individual queries and flushing each to the database individually would be ridiculously expensive. Especially with replication.

Turning on transactions though makes a big difference. At 3,000 rows it took 1.5x more and at 30,000 rows 2.34x. [continued] – Nov 26 '17 at 22:04 •. But you were right about it being fast (with transactions). At both 3,000 and 30,000 rows it was faster than everything but the INSERT method. There is absolutely no way you are going to get better results from running 1 query than 30,000 queries, even if they are batched in a special MySQL API call.

Running only 300 rows, it was MUCH faster than all other methods (to my surprise), which follows about the same graph curve as the CASE method. This being faster can be explained by 2 ways. The first being that the INSERT method essentially always inserts 2 rows due to the 'ON DUPLICATE KEY [cont] – Nov 26 '17 at 22:18 •.