How Can I Efficiently Update Existing Records While Inserting New Ones Using MySQL's `INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE`?

Using "INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE" to Update Existing Records

When inserting data into a table where certain columns may already exist, you may want to update those columns with new values. MySQL provides a convenient syntax for such scenarios using the "ON DUPLICATE KEY UPDATE" clause.

In your query, you have an "INSERT INTO" statement followed by a "SELECT" statement. The goal is to insert new rows into the "lee" table and update existing rows that match a unique key. To do this, you need to specify which columns to update in the "ON DUPLICATE KEY UPDATE" clause.

MySQL interprets the part before the equals sign (=) as referring to the columns named in the "INSERT INTO" clause. The part after the equals sign refers to the columns in the "SELECT" clause.

For example:

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 in the "INSERT INTO" clause that are not updated are "exp_id", "created_by", "location", "animal", "starttime", and "endtime". All other columns, such as "entct", "inact", "inadur", "inadist", etc., are updated with values from the "SELECT" clause.

By using the proper syntax, you can perform inserts and updates in a single query, ensuring that your data is kept up to date efficiently.

The above is the detailed content of How Can I Efficiently Update Existing Records While Inserting New Ones Using MySQL's `INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE`?. For more information, please follow other related articles on the PHP Chinese website!