INSERT and return ID, or if DUPLICATE KEY return existing ID in MySQL

Question

I have a "tags" table, which has a UNIQUE on the "tags" column, to prevent duplicates.

I have a "bookmarks_tags" table, which relates bookmarks to tags, and it has a UNIQUE on the "bookmark_id" and "tag_id" columns, to prevent duplicates.

However, I need people to be able to add the same tag, and to accomplish this, I need some means of retrieving the ID of the existing tag to use as reference in the "bookmarks_tags" table.

Is there a way of writing an INSERT so that if it detects a duplicate, it returns the ID of that duplicate? Or, alternatively, would an INSERT ... SELECT be more appropriate for "bookmarks_tags" table?

The key thing here is that it has to work under both conditions; add as new, or retrieve old.

Also, LAST_INSERT_ID() is useless in this scenario, as the tag in question could have been added at any time.

Any ideas?

I can write a SELECT query, but that isn't the problem. I need to know if an INSERT can accomplish the same thing, saving the extra coding; I'm using a framework, so there's several files that would need changing. — Wayne Smallman
– Wayne Smallman, Commented May 4, 2013 at 13:36
Possible duplicate of MySQL ON DUPLICATE KEY - last insert id? — Lorenz Meyer
– Lorenz Meyer, Commented Jun 14, 2017 at 8:54

Enissay · Accepted Answer · 2018-02-23 23:16:54Z

23

$query = "INSERT INTO table SET unique1=value1 AND unique2=value2… ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)";
$insert_result = mysql_query($query);
$id = mysql_insert_id();
return $id;

Insert Into Database or Return ID of Duplicate Row in MySQL (Backup link @WebArchive)

edited Feb 23, 2018 at 23:16

Enissay

4,9633 gold badges33 silver badges59 bronze badges

answered May 28, 2013 at 16:19

Pham Quang

3311 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Maciek Over a year ago

Is there a way to distinguish a new and a duplicate returned id?

Jared Dunham Over a year ago

Broken link. :(

Enissay Over a year ago

@JaredDunham backup link added ;-)

DAG Over a year ago

Do note this will cause autoincrements to increment

Ross Smith II · Accepted Answer · 2013-05-04 15:23:05Z

6

One way to do this is using INSERT IGNORE:

INSERT IGNORE INTO tags (tags,...) VALUES (the_new_tags, ...);

SELECT tag_id FROM tags WHERE tags=the_new_tags;

answered May 4, 2013 at 15:23

Ross Smith II

12.2k1 gold badge41 silver badges46 bronze badges

4 Comments

Wayne Smallman Over a year ago

Okay, that's the way I'm going. It's the one with the least hassle. Ross, thanks.

Chris Over a year ago

Wouldn't @Pham Duc Quang method be better? Only requires one query.

Ross Smith II Over a year ago

Yes, INSERT INTO tags SET tags=the_new_tags ON DUPLICATE KEY UPDATE tag_id=LAST_INSERT_ID(tag_id) should work better, but I haven't tested it.

EdL Over a year ago

Insert IGNORE is a bad idea since it will ignore all errors (type, length etc.), instead is INSERT ON DUPLICATE KEY UPDATE key=key but will correctly throw other errors. dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html See other answer: stackoverflow.com/a/16796885/3343553

Collectives™ on Stack Overflow

INSERT and return ID, or if DUPLICATE KEY return existing ID in MySQL

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related