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

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.

Answer 2

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 3

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;

Answer 4

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 5

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 !

Answer 6

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.

Answer 7

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 8

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

Answer 9

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.

Answer 10

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

Answer 11

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.

Answer 12

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 13

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