mysql insert if row does not exist already in a table with NO UNIQUE FIELDS

mysql insert conditional not-exists
Glenn picture Glenn · Dec 28, 2009 · Viewed 8.4k times · Source

Looking for a while now already for how to accomplish this.

Seems that all solutions need unique fields with indexes.

Answer

afftee picture afftee · Dec 28, 2009

there is no IF NOT EXISTS syntax in INSERT, but you could make use of the ON DUPLICATE KEY mechanism. Assuming you create a unique index on firstname, lastname, your update might read:

INSERT INTO tb (firstname, lastname) 
VALUES ('Jack', 'Doe') 
ON DUPLICATE KEY UPDATE lastname = lastname;

which renders the insert neutral.

Related questions

MySQL Conditional Insert

I am having a difficult time forming a conditional INSERT I have x_table with columns (instance, user, item) where instance ID is unique. I want to insert a new row only if the user already does not have a …

Read More
"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either: ON DUPLICATE KEY UPDATE which implies an unnecessary update …

Read More
How to set initial value and auto increment in MySQL?

How do I set the initial value for an "id" column in a MySQL table that start from 1001? I want to do an insert "INSERT INTO users (name, email) VALUES ('{$name}', '{$email}')"; Without specifying the initial …

Read More