MySQL Conditional Insert

Question

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 1

Insert into x_table (instance, user, item) values (919191, 123, 456) 
    where ((select count(*) from x_table where user=123 and item=456) = 0);

The syntax may vary depending on your DB...

Answer 2

With a UNIQUE(user, item), do:

Insert into x_table (instance, user, item) values (919191, 123, 456) 
  ON DUPLICATE KEY UPDATE user=123

the user=123 bit is a "no-op" to match the syntax of the ON DUPLICATE clause without actually doing anything when there are duplicates.

Answer 3

You can also use INSERT IGNORE which silently ignores the insert instead of updating or inserting a row when you have a unique index on (user, item).

The query will look like this:

INSERT IGNORE INTO x_table(instance, user, item) VALUES (919191, 123, 456)

You can add the unique index with CREATE UNIQUE INDEX user_item ON x_table (user, item).

Answer 4

Slight modification to Alex's response, you could also just reference the existing column value:

Insert into x_table (instance, user, item) values (919191, 123, 456) 
  ON DUPLICATE KEY UPDATE user=user

Answer 5

You can use the following solution to solve your problem:

INSERT INTO x_table(instance, user, item) 
    SELECT 919191, 123, 456
        FROM dual
        WHERE 123 NOT IN (SELECT user FROM x_table)

Answer 6

Have you ever tried something like that?

INSERT INTO x_table
SELECT 919191 as instance, 123 as user, 456 as item
FROM x_table
WHERE (user=123 and item=456)
HAVING COUNT(*) = 0;