Get last ID of a newly inserted element, or of an existing one

Question

I have a following map table, with a unique key on the name column:

id | name
1    aaa
2    bbb
3    ccc

I want to get a newly created ID if I insert a new value to this table, like this:

INSERT IGNORE INTO map VALUES(null, 'ddd');

I know I can do this with getLastId() function. But I also want to get ID if a name already exists, and getLastId() function returns 0 in such case:

INSERT IGNORE INTO map VALUES(null, 'ccc');

Is this possible to do with one SQL, and without checking if a record exists, etc?

`getLastId()` won't give you the value of the "id" column even on insert either. The ID in your case is "name". And you have the "name" already. That's more a conceptual thing on what an *Identity* is actually I'd say. — hakre, Dec 24 '16 at 10:56

score 1 · Answer 1 · answered Dec 24 '16 at 11:00

1

I think you need to go the extra mile for that one:

check if exists - when so: get ID of that name
if not: SELECT LAST_INSERT_ID();

answered Dec 24 '16 at 11:00

helle

11,183
9
56
83

score 1 · Answer 2 · edited May 23 '17 at 11:46

Depends on what qualifies as a single query.

When you make use of INSERT INTO ... ON DUPLICATE KEY UPDATE you can control the LAST_INSERT_ID() in such a manner that it will return the new id on insert and the id of the row "updated" on update:

INSERT INTO map (name) VALUE ('ccc') 
  ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);

That will make the LAST_INSERT_ID() then return the map.id of the row you're interested in.

Whatever the API is that you name as the getLastId() (I don't know it so I can't provide more pointers on it) this may not work and you would then need to fire a second query to obtain it:

SELECT LAST_INSERT_ID();

See as well:

MySQL ON DUPLICATE KEY - last insert id? (Stackoverflow Q&A, Apr 2009)

Get last ID of a newly inserted element, or of an existing one

2 Answers2