MySQL Conditional Insert

user_96723 0
Viewed 104647

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.

insert conditional mysql
13 Answers

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.

user_151680

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.

user_958100

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
user_909270

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.

user_1114690

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

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.

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

user_788920
Related