MySQL ON DUPLICATE KEY – last insert id?

by

Check this page out: https://web.archive.org/web/20150329004325/https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
At the bottom of the page they explain how you can make LAST_INSERT_ID meaningful for updates by passing an expression to that MySQL function.

From the MySQL documentation example:

If a table contains an AUTO_INCREMENT column and INSERT … UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. MySQL select 10 random rows from 600K rows fast
  3. How do you use the “WITH” clause in MySQL?
  4. Simulate lag function in MySQL
  5. mysql error 1364 Field doesn’t have a default values
  6. SQL: What is the default Order By of queries?
  7. MySQL Split Comma Separated String Into Temp Table
  8. How to group by week in MySQL?
  9. Can I use a function for a default value in MySql?
  10. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  11. Get record counts for all tables in MySQL database
  12. MySQL – Make an existing Field Unique
  13. How can I simulate an array variable in MySQL?
  14. mysql2 gem compiled for wrong mysql client library
  15. Datetime equal or greater than today in MySQL
  16. MySQL – length() vs char_length()
  17. How can I suppress column header output for a single SQL statement?
  18. Use a trigger to stop an insert or update
  19. mysql query show multiple tables from one ID column
  20. Modeling Product Variants
  21. MySQL: ALTER IGNORE TABLE gives “Integrity constraint violation”
  22. How do I find which transaction is causing a “Waiting for table metadata lock” state?
  23. MySQL LIKE query with underscore
  24. How do I refer to capture groups in a MySQL regex?
  25. What is the default MySQL JOIN behaviour, INNER or OUTER?
  26. How to get the mysql table columns data type?
  27. How to export / dump a MySql table into a text file including the field names (aka headers or column names)
  28. ALTER TABLE ADD COLUMN takes a long time
  29. Creating MYSQL Procedure in Laravel 4 Migrations
  30. MySQL: Computed Column

Leave a Comment