MySQL Conditional Insert

mysql insert conditional
The Unknown picture The Unknown · May 27, 2009 · Viewed 89.7k times · Source

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 given item.

For example trying to insert instance=919191 user=123 item=456

Insert into x_table (instance, user, item) values (919191, 123, 456) 
    ONLY IF there are no rows where user=123 and item=456

Any help or guidance in the right direction would be much appreciated.

Answer

Jonathan Leffler picture Jonathan Leffler · May 27, 2009

If your DBMS does not impose limitations on which table you select from when you execute an insert, try:

INSERT INTO x_table(instance, user, item) 
    SELECT 919191, 123, 456
        FROM dual
        WHERE NOT EXISTS (SELECT * FROM x_table
                             WHERE user = 123 
                               AND item = 456)

In this, dual is a table with one row only (found originally in Oracle, now in mysql too). The logic is that the SELECT statement generates a single row of data with the required values, but only when the values are not already found.

Alternatively, look at the MERGE statement.

Related questions

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

Looking for a while now already for how to accomplish this. Seems that all solutions need unique fields with indexes.

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