How to Perform Bulk Updates in MySQL Using INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE?

Performing Bulk Updates with INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

When attempting to insert new data into a table, there may be instances where existing records with identical unique keys need to be updated with the latest values. MySQL provides a convenient syntax for such scenarios using the INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE statement.

In your case, you need to update all columns except exp_id, created_by, location, animal, starttime, and endtime. The syntax for this update clause is:

ON DUPLICATE KEY UPDATE <column_name>=<select_column_name>, ...

Here, refers to the columns in the INSERT INTO clause, while refers to the corresponding columns in the SELECT clause.

Based on your query, the complete statement would look like this:

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, 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.emptyct, emptydur=t.emptydur;

With this statement, MySQL will insert the data from tmp into lee, and if a duplicate key is encountered, it will update the specified columns with the values from the SELECT statement.

The above is the detailed content of How to Perform Bulk Updates in MySQL Using INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE?. For more information, please follow other related articles on the PHP Chinese website!