How to Correctly Specify Columns in MySQL's `ON DUPLICATE KEY UPDATE` Clause with `INSERT INTO ... SELECT ...`?

Resolving Column Update Syntax in "INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE"

This query inserts data into the lee table from the tmp table, updating existing rows if a unique key conflict occurs. However, the syntax for the ON DUPLICATE KEY UPDATE clause appears unclear.

Solution

MySQL assumes that the left-hand side of the equation after ON DUPLICATE KEY UPDATE references columns in the INSERT INTO clause, while the right-hand side references columns in the SELECT clause.

For instance, consider the following query:

INSERT INTO lee(exp_id, created_by, location, animal, starttime, endtime, entct, 
                inact, inadur, inadist, 
                smlct, smldur, smldist, 
                larct, lardur, lardist, 
                emptyct, emptydur)
SELECT id, uid, t.location, t.animal, t.starttime, t.endtime, t.entct, 
       t.inact, t.inadur, t.inadist, 
       t.smlct, t.smldur, t.smldist, 
       t.larct, t.lardur, t.lardist, 
       t.emptyct, t.emptydur 
FROM tmp t WHERE uid=x
ON DUPLICATE KEY UPDATE entct=t.entct, inact=t.inact, ...

In this query, the left-hand side of the ON DUPLICATE KEY UPDATE clause matches the columns named in the INSERT INTO clause. The columns before the equals sign represent the columns in the leo table, and the columns after the equals sign represent the values to be updated from the SELECT clause.

The above is the detailed content of How to Correctly Specify Columns in MySQL's `ON DUPLICATE KEY UPDATE` Clause with `INSERT INTO ... SELECT ...`?. For more information, please follow other related articles on the PHP Chinese website!