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.
dual need to be created before hand in this case or is it some kind of special "temporary" table created by the query for the use of the query alone?dual already, you need to create it. CREATE TABLE dual ( dummy CHAR(1) DEFAULT 'x' NOT NULL CHECK (dummy = 'x') PRIMARY KEY ); INSERT INTO dual VALUES('x'); REVOKE ALL ON dual FROM PUBLIC; GRANT SELECT ON dual TO PUBLIC;
dual already" statement has a false precedent (because MySQL does already have the dual) and remains a true statement overall (because in logic IF false THEN consequent evaluates to true regardless of the consequent). But thanks for noting that DUAL does indeed exist in MySQL.SELECT COUNT(*) FROM dual you always get 1, and if you SELECT 'foo' FROM dual you always get foo. But you can't SELECT * FROM dual and you can't DESCRIBE dual or anything like that. I haven't checked, but I also don't think you can revoke permissions on dual, either. So it's worth pointing out that it works as you expect... except when it doesn't ;)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.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where... (running 5.6.34)from dual before where.
FROM DUAL is the default if no tables were referenced (see this documentation).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
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...
