Update multiple columns using mysql UPDATE- MySQL Development. Visit Dev Articles to discuss update multiple columns using mysql UPDATE.

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. Printer Install Windows 7 Without Admin Rights. 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. Php Upload File Without Submit. 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. Xerox 5220 Personal Copier Manual.

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

Hp Intelligent Provisioning Recovery Media Usb on this page. 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.