MySQL insert on duplicate key; delete?

user_1417500 0
Viewed 22234

Is there a way of removing record on duplicate key in MySQL?

Say we have a record in the database with the specific primary key and we try to add another one with the same key - ON DUPLICATE KEY UPDATE would simply update the record, but is there an option to remove record if already exists? It is for simple in/out functionality on click of a button.

duplicate-removal sql-delete key mysql
5 Answers

to be more clear with mySql:
values can be from same table:
replace into table1 (column1,column2) select (val1,val2) from table1

or

values can be from another table:
replace into table1 (column1,column2) select (val1,val2) from table2

or

values can be from any table with condition:
replace into table1 (column1,column2) select (val1,val2) from table1 where <br>column3=val3 and column4=val4 ...


or

also remember values can be static with table name for namesake:
replace into table1 (column1,column2) select (123,"xyz") from table1

no error will be thrown even if the update results in duplicate entry, as it will be replaced.
(remember) only autoincrement value will be increased;

and

if you have column with data-type "TIMESTAMP" with "on update CURRENT_TIMESTAMP", it will have no effect;

user_21738900

Yes of course there is a solutions in MySQL for your problem.

If you want to delete or skip the new inserting record if there already a duplicate record exists in the key column of the table, you can use IGNORE like this:

insert ignore into mytbl(id,name) values(6,'ron'),(7,'son');

Here id column is primary key in the table mytbl. This will insert multiple values in the table by deleting or skipping the new duplicate records.

Hope this will fulfill your requirement.

user_30083490
Related