MySQL insert on duplicate key; delete?

mysql key duplicate-removal sql-delete
Spencer Mark picture Spencer Mark · Jul 15, 2012 · Viewed 18.5k times · Source

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.

Answer

James picture James · Jan 9, 2015

It's a work-around, but it works:

Create a new column and call it do_delete, or whatever, making it a tiny-int. Then do On Duplicate Key Update do_delete = 1;

Depending on your MySQL version/connection, you can execute multiple queries in the same statement. However, if not, just run a separate query immediately afterwords. Either way, the next query would just be: Delete From [table] Where do_delete = 1;. This way, if its a new entry, it will not delete anything. If it was not a new entry, it will then mark it for deletion then you can delete it.

Related questions

Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT when creating MySQL tables? How would I use them?

Read More
Get the new record primary key ID from MySQL insert query?

Let's say I am doing a MySQL INSERT into one of my tables and the table has the column item_id which is set to autoincrement and primary key. How do I get the query to output the value of …

Read More
SQL keys, MUL vs PRI vs UNI

What is the difference between MUL, PRI and UNI in MySQL? I'm working on a MySQL query, using the command: desc mytable; One of the fields is shown as being a MUL key, others show up as UNI or PRI. …

Read More