MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

by

Beginning with MySQL 8.0.19 you can use an alias for that row (see reference).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS new
ON DUPLICATE KEY UPDATE
    age = new.age
    ...

For earlier versions use the keyword VALUES (see reference, deprecated with MySQL 8.0.20).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...

More Related Contents:

  1. MySQL remove duplicates from big database quick
  2. Join vs. sub-query
  3. Find records from one table which don’t exist in another
  4. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  5. Natural Sort in MySQL
  6. MySQL get row position in ORDER BY
  7. Subqueries vs joins
  8. MySQL Results as comma separated list
  9. MySQL unknown column in ON clause
  10. Delete duplicate records in SQL Server?
  11. MySQL not using indexes with WHERE IN clause?
  12. SQL: Find the max record per group [duplicate]
  13. Maintaining order in MySQL “IN” query
  14. MySQL: What is a reverse version of LIKE?
  15. Select TOP X (or bottom) percent for numeric values in MySQL
  16. MySQL select where column is not empty
  17. MySQL: GROUP_CONCAT with LEFT JOIN
  18. Hamming distance on binary strings in SQL
  19. Is it possible to have a default parameter for a mysql stored procedure?
  20. Renaming foreign-key columns in MySQL
  21. How to convert from varbinary to char/varchar in mysql
  22. MySQL GROUP BY behavior
  23. How to select records without duplicate on just one field in SQL?
  24. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  25. Update multiple rows with one query?
  26. What does collation mean?
  27. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  28. sql pulling a row for next or previous row of a current row
  29. Row Rank in a MySQL View
  30. Select Rows with Maximum Column Value group by Another Column

Leave a Comment