How Does MySQL Handle INSERT ... SELECT ... ON DUPLICATE KEY UPDATE Syntax?

INSERT ... SELECT ... ON DUPLICATE KEY UPDATE Syntax Clarification

When performing an INSERT query where some columns may already exist in the target table, an ON DUPLICATE KEY UPDATE clause can be used. This clause allows you to specify the update criteria and the values to update.

The syntax for the UPDATE clause in such a query is as follows:

ON DUPLICATE KEY UPDATE column1=value1, column2=value2, ...;

The key aspect to understand is how MySQL references the values being inserted and updated. By default, the part before the equals sign references the columns named in the INSERT INTO clause, while the part after the equals sign references the columns named in the SELECT clause.

For example, 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 columns named in the INSERT INTO clause will be used for column assignment by default:

• exp_id
• created_by
• location
• animal
• starttime
• endtime

The columns named in the SELECT clause will be used to specify update values:

• 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

Columns that are not included in the ON DUPLICATE KEY UPDATE clause will not be updated.

The above is the detailed content of How Does MySQL Handle INSERT ... SELECT ... ON DUPLICATE KEY UPDATE Syntax?. For more information, please follow other related articles on the PHP Chinese website!