Menu
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?
489
Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
By definition, atomicity requires that each transaction is an all or nothing. So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
$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)
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
