mySQL insert only when not exists but with more conditions

Question

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.

Replace VALUES with SELECT and remove the parentheses from (5,9936). — forpas
– forpas, Commented Aug 29, 2020 at 9:54

Luuk · Accepted Answer · 2020-08-29 09:51:09Z

1

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.

answered Aug 29, 2020 at 9:51

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bjorn T Over a year ago

Thanks!!! This does exactly what it needs to do. Hope the same query will work (for other tables) in prepared statements.

Collectives™ on Stack Overflow

mySQL insert only when not exists but with more conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related