150

I have the following query:

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE a=1

I want the ID of either the insert or the update. Usually I run a second query in order to get this as I believe insert_id() only returns the 'inserted' ID and not the updated ID.

Is there a way to INSERT/UPDATE and retrieve the ID of the row without running two queries?

Ken Wayne VanderLinde
  • 18,915
  • 3
  • 47
  • 72
thekevinscott
  • 5,263
  • 10
  • 44
  • 57
  • 3
    Rather than supposing, why don't you test it yourself? The SQL in the edit above does work, and through my testing is faster than catching an insertion fail, using INSERT IGNORE, or selecting to see if there is a duplicate first. – Michael Fenwick Dec 28 '11 at 01:26
  • 5
    WARNING: The solution proposed works, but the auto_increment value continues to increment, even if there is no insert. If the duplicate key happens often, you may want to run `alter table tablename AUTO_INCREMENT = 0;` after the above query, to avoid big gaps in your id values. – Frank Forte Apr 22 '16 at 06:00
  • 1
    @FrankForte You are joking about `alter table` at run time, with concurrent users, in production, right? – doug65536 Nov 26 '21 at 00:29

7 Answers7

203

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;
Naktibalda
  • 13,705
  • 5
  • 35
  • 51
fredrik
  • 13,282
  • 4
  • 35
  • 52
  • 2
    Somehow I missed that when looking at that page. So the update portion appears as : UPDATE id=LAST_INSERT_ID(id) And that works great. Thanks! – thekevinscott Apr 22 '09 at 22:09
  • 7
    It is said that php function mysql_insert_id() returns correct value in both cases: http://www.php.net/manual/en/function.mysql-insert-id.php#59718. – jayarjo Jun 10 '10 at 13:09
  • What in seven hells means that the value is "not meaningful"?? :-| – Petr Peller Jun 19 '13 at 13:53
  • 2
    @PetrPeller - well, without looking at the MySQL internals, it probably means that it will produce a value, but that value is not related to the query you just ran. In other words, a problem that is a pain to debug. – Jason Jul 23 '13 at 13:46
  • 19
    After 5.1.12 this is supposedly no longer necessary, however I found an exception to that today. If you have an autoincrement pk, and a unique key on say an email address, and the 'on duplicate update' triggers based on the email address, note that the last_insert_id' will NOT be the autoincrement value of the updated row. It appears to be the most recently inserted autoincrement value. This makes a huge difference. The work around is the same as shown here, namely to use id=LAST_INSERT_ID(id) in the updating query. – sckd Sep 19 '14 at 09:34
  • 1
    On 5.5 @sckd's comment still holds true. – e18r Jul 08 '15 at 17:39
  • 3
    Now the quoted part has changed, for [the Docs](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html): `If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value.` – Erenor Paz May 16 '18 at 09:16
  • will this work for pdo and php? $pdo->lastInsertId(); – fpilee Dec 17 '18 at 22:42
  • @ErenorPaz Of course, `LAST_INSERT_ID()` always returns the `AUTO_INCREMENT` value. But which one? What does it do before and after insertion or update? – TheRealChx101 Apr 14 '23 at 11:05
40

To be exact, if this is the original query:

INSERT INTO table (a) VALUES (0)
 ON DUPLICATE KEY UPDATE a=1

and 'id' is the auto-increment primary key than this would be the working solution:

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), a=1

Is all here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

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.

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
Aleksandar Popovic
  • 928
  • 8
  • 17
  • 8
    Yes, see the accepted answer for the same what you said. No need to revive 3 year old posts. Thanks for your effort anyway. – fancyPants Oct 04 '12 at 09:40
  • 1
    @tombom the only reason why i posted this answer is because the accepted answer is not correct - it won't work if there is nothing to update. – Aleksandar Popovic Nov 09 '12 at 23:05
2

You might look at REPLACE, which is essentially a delete/insert if the record exists. But this would change the auto increment field if present, which could break relationships with other data.

Brent Baisley
  • 962
  • 1
  • 6
  • 4
2

I don't know what is your version of MySQL but with InnoDB, there was bug with autoinc

bug in 5.1.20 and corrected in 5.1.23 http://bugs.mysql.com/bug.php?id=27405

bug in 5.1.31 and corrected in 5.1.33 http://bugs.mysql.com/bug.php?id=42714

Hugues Van Landeghem
  • 6,755
  • 3
  • 34
  • 59
1

I have come across a problem, when ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) increment the primary key by 1. So the id of the next input within the session will be incremented by 2

Oleksii Zymovets
  • 690
  • 8
  • 14
0

It's worth noting, and this might be obvious (but I'll say it anyway for clarity here), that REPLACE will blow away the existing matching row before inserting your new data. ON DUPLICATE KEY UPDATE will only update the columns you specify and preserves the row.

From the manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Greg K
  • 10,770
  • 10
  • 45
  • 62
0

The existing solutions work if you use autoincrement. I have a situation where the user can define a prefix and it should restart the sequence at 3000. Because of this varied prefix, I cannot use autoincrement, which makes last_insert_id empty for inserts. I solved it with the following:

INSERT INTO seq_table (prefix, id) VALUES ('$user_prefix', LAST_INSERT_ID(3000)) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();

If the prefix exists, it will increment it and populate last_insert_id. If the prefix does not exist, it will insert the prefix with the value 3000 and populate last_insert_id with 3000.

Aaron
  • 414
  • 4
  • 16