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

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.

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).

Have you ever tried something like that?

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

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.

In case you don't want to set a unique constraint, this works like a charm :

INSERT INTO `table` (`column1`, `column2`) SELECT 'value1', 'value2' FROM `table` WHERE `column1` = 'value1' AND `column2` = 'value2' HAVING COUNT(`column1`) = 0

Hope it helps !

What you want is INSERT INTO table (...) SELECT ... WHERE .... from MySQL 5.6 manual.

In you case it's:

INSERT INTO x_table (instance, user, item) SELECT 919191, 123, 456 
WHERE (SELECT COUNT(*) FROM x_table WHERE user=123 AND item=456) = 0

Or maybe since you're not using any complicated logic to determiante whether to run the INSERT or not you could just set a UNIQUE key on the combination of these two columns and then use INSERT IGNORE.

If you add a constraint that (x_table.user, x_table.item) is unique, then inserting another row with the same user and item will fail.

eg:

mysql> create table x_table ( instance integer primary key auto_increment, user integer, item integer, unique (user, item));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x_table (user, item) values (1,2),(3,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into x_table (user, item) values (1,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into x_table (user, item) values (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 2

Although it's good to check for duplication before inserting your data I suggest that you put a unique constraint/index on your columns so that no duplicate data can be inserted by mistake.

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)

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

So this one stands for PostgreSQL

INSERT INTO x_table
SELECT NewRow.*
FROM (SELECT 919191 as instance, 123 as user, 456 as item) AS NewRow
LEFT JOIN x_table
ON x_table.user = NewRow.user AND x_table.item = NewRow.item
WHERE x_table.instance IS NULL

I have found out today that a SELECT statement can have a WHERE condition even if it has no FROM clause and does not read any tables at all.

This makes it very easy to conditionally insert something using this construct:

SELECT ... INTO @condition;
-- or if you prefer: SET @condition = ...

INSERT INTO myTable (col1, col2) SELECT 'Value 1', 'Value 2' WHERE @condition;

Tested this on MySQL 5.7 and MariaDB 10.3.

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