How to Update Existing Rows During an INSERT Using MySQL's ON DUPLICATE KEY UPDATE?

Using "INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE"

When performing an insert operation with MySQL, it is possible to update existing rows if a unique key conflict occurs. This can be achieved using the "INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE" syntax.

In the given query, it is intended to insert rows from a temporary table ('tmp') into the 'lee' table. If a row with the same unique key ("id" in this case) already exists in 'lee', the query should update specific columns to the values from the 'tmp' table.

The syntax for the UPDATE clause in this scenario is as follows:

ON DUPLICATE KEY UPDATE column1=SELECT_column1, column2=SELECT_column2, ...

Here, "column1" refers to a column in the 'lee' table, and "SELECT_column1" refers to the corresponding column in the SELECT clause. By specifying these pairs, MySQL determines which columns to update with the new values from the SELECT.

For the provided query, the UPDATE clause would look like:

ON DUPLICATE KEY UPDATE entct=t.entct, inact=t.inact, inadur=t.inadur, inadist=t.inadist, smlct=t.smlct, smldur=t.smldur, smldist=t.smldist, larct=t.larct, lardur=t.lardur, lardist=t.lardist, emptyct=t.emptydur, emptydur=t.emptydur

This ensures that the columns "entct", "inact", "inadur", and so on, are updated with the values from the corresponding columns in the temporary table when a duplicate key is encountered.

The above is the detailed content of How to Update Existing Rows During an INSERT Using MySQL's ON DUPLICATE KEY UPDATE?. For more information, please follow other related articles on the PHP Chinese website!