MySQL ON DUPLICATE KEY - last insert id?

Question

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?

Thanks, Kevin

Update: So the answer looks like this:

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

Thanks all!

Answer 1

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.

Answer 2

Dumb question: If you're using an auto-incremented index, why would it already exist?

Answer 3

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

Answer 4

Check this page out: http://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.

Answer 5

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.