CODEWITHSUNDEEP
mysqlsql-insert
Bjorn T
Bjorn T

Reputation: 53

mySQL insert only when not exists but with more conditions

I have a table product_pushjobs with 5 columns: id, user, product, connection, done` ID is primary and auto increment.

For insert, I only fill in user and product (sometimes connection, depending on situation)

I want to prevent the insert when there is an other row with user and product, but only if done IS NULL. So the table can have a million rows of the same user and product, but not when there is one that has a done is NULL .

I found a lot different suggestions here on this site, but not exactly the same as mine. I've tried this, but have me an error:

INSERT INTO product_pushjobs (user, product) 
VALUES (5,9936) 
WHERE NOT EXISTS (
    SELECT id FROM product_pushjobs 
    WHERE user=5 AND product=9936 AND connection IS NULL AND done IS NULL
)

mysql error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM product_pushjobs WHERE user=5 AND product=9936 A' at line 1

I'm guessing it will be a stupid problem, but one that I need to fix fast.

Upvotes: 0

Views: 845

Answers (1)

Luuk
Luuk

Reputation: 14929

The INSERT statement does not have the possibility to add a WHERE-clause to it.

So, you could to:

INSERT INTO product_pushjobs (user, product) 
    SELECT 5,9936 WHERE (SELECT 1 
                           FROM product_pushjobs 
                           WHERE user=5 
                             AND product=9936 
                             AND connection IS NULL 
                             AND done IS NULL) IS NULL

The result from the query SELECT 5,9936.... is used for input to the INSERT statement, but there is no result. The WHERE-clause makes sure only a record is returned if the record-to-insert is not already present.

SELECT 1 could also be SELECT id, but since nothing is done with id, i choose to put 1 in that place.

Upvotes: 1

Related Questions