INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE

by

MySQL will assume the part before the equals references the columns named in the INSERT INTO clause, and the second part references the SELECT columns.

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, ...

More Related Contents:

  1. Insert into a MySQL table or update if exists
  2. MySQL trigger On Insert/Update events
  3. Update a column in MySQL
  4. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  5. How to pivot a MySQL entity-attribute-value schema
  6. Combining UNION and LIMIT operations in MySQL query
  7. MySQL find_in_set with multiple search string
  8. Convert MySQL schema to Github Wiki?
  9. mysql select from n last rows
  10. Install MySQL on Ubuntu without a password prompt
  11. Exporting results of a Mysql query to excel?
  12. Capitalize first letter of each word, in existing table
  13. use mysql SUM() in a WHERE clause
  14. A constraint to prevent the insert of an empty string in MySQL
  15. Search all columns of a table using a single where condition with single keyword in mysql
  16. MySQL trigger to update a field to the value of id
  17. How to get a list of MySQL views?
  18. Case statement in MySQL
  19. MySQL: Inner join vs Where [duplicate]
  20. MySQL ‘UPDATE ON DUPLICATE KEY’ without a unique column?
  21. Selecting from two tables with inner join and limit [duplicate]
  22. MySQL substring extraction using delimiter
  23. Convert Unix timestamp into human readable date using MySQL
  24. MySQL : retrieve a large select by chunks
  25. MySQL – insert data from another table merged with constants
  26. Database corruption with MariaDB : Table doesn’t exist in engine
  27. How to SELECT by MAX(date)?
  28. MySQL matching unicode characters with ascii version
  29. MySQL’s lower_case_table_names won’t change
  30. Optimistic locking in MySQL

Leave a Comment