Re: Performance of ON DUPLICATE KEY and other options?

[Mark Modrall]

Hi Jaime...

No problem... Actually, in the comments for the ON DUPLICATE KEY UPDATE man page, there is a clue on how to do this. LAST_INSERT_ID() can also be explicitly set if you provide a parameter. The example in the man comments was to do something like this:

INSERT INTO table (a,b,c) SELECT ... ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

taken together, the last SELECT will return the last id touched whether new or not.

You can execute the two statements together as a compound and then ExecuteScalar() to get the result, but there is a bug I have in (on the .Net connector at least) that Prepare() breaks if you have a compound statement.

If you're running MySql 5, you could also put the two statements in a stored procedure and execute that.

-Mark