CODEWITHSUNDEEP
mysqldatabasekey
Mitya
Mitya

Reputation: 34596

MySQL on duplicate key... get existing ID?

I'm using ON DUPLICATE KEY UPDATE to handle duplicate inserts on a table, in order that they are discarded.

In my case it's a simple table storing tags:

This is working fine, but I need to retrieve the ID - either the insert ID, on successful insert, or the existing ID, if it's a duplicate.

I'm getting insert ID = 0 where ON DUPLICATE KEY UPDATE fires, which I guess is expected behaviour since no insert took place.

Is there anyway I can get the existing ID, or am I headed to a separate read query?

Upvotes: 3

Views: 2269

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You could add a third column ModifiedDate and use that:

insert into t(id, tag)
    select id, tag
    on duplicate key update ModifiedDate = now();

This will ensure that an update really occurs, and in turn, that LAST_INSERT_ID() returns a value.

Upvotes: 2

Related Questions