Conditional INSERT INTO MySQL - WHERE NOT EXISTS

Question

I am trying to do an INSERT INTO query when the customer does not already have a record of purchasing a product.

I have tried the below SQL, but it doesn't seem to work.

$queryAddPurchase = "INSERT INTO purchase (UserID, Product, Price) 
              VALUES ('$userID', '$product', '$price')
              WHERE NOT EXISTS(SELECT Product
                                       FROM purchase
                       WHERE Product = '$product'
                       AND UserID = '$userID')";

The error I am receiving is as follows:

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 NOT EXISTS(SELECT Product FROM purchase ' at line 3

Any advice would be greatly appreciated!

You could set up `UserID` and `Product` as a unique key and then use one of `INSERT IGNORE`, `REPLACE INTO` or `ON DUPLICATE KEY UPDATE`. — MichaelRushton, Mar 26 '13 at 21:21
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 NOT EXISTS(SELECT Product FROM purchase ' at line 3 — New_programmer, Mar 26 '13 at 21:23
possible duplicate http://stackoverflow.com/questions/15622121/mysql-query-insert-where-not-exists — DevelopmentIsMyPassion, Mar 26 '13 at 21:28
@AshReva which is a duplicate of http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — scones, Mar 26 '13 at 21:30

score 1 · Answer 1 · answered Mar 26 '13 at 21:23

On the assumption, that a user may only buy one of each product (ever and for all products).

ALTER TABLE purchase ADD UNIQUE KEY (`UserID`, `Product`); -- run this just once. this changes the table

INSERT IGNORE INTO purchase (UserID, Product, Price) VALUES ('$userID', '$product', '$price');

Be aware, that this then prevents him from buying any product multiple times, which might not be the desired outcome.

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

If possible, add a UNIQUE constraint to the table. Something like ...

alter table purchase add unique user_product (UserID, ProductID);

And your conditional insert becomes trivial:

INSERT IGNORE INTO purchase (UserID, Product, Price) 
    VALUES ('$userID', '$product', '$price')

If this is not a suitable solution for you, use the selected answer here: MySQL Conditional Insert

That answer, in brief:

INSERT INTO purchase (UserID, Product, Price) 
    SELECT '$userID', '$product', '$price'
    FROM dual
        WHERE NOT EXISTS (
            SELECT * FROM purchase
            WHERE UserID='$userID'
                AND ProductID='$product'
        )

edited May 23 '17 at 12:24

Community

1
1

answered Mar 26 '13 at 21:24

svidgen

13,099
4
31
51

And I should mention, this is *probably* ideal if your schema allows for it, because you'll likely want those columns indexed for the lookup anyway. – svidgen Mar 26 '13 at 21:28
mysql does not allow that. *on the alternative* – scones Mar 26 '13 at 21:31
@scones Are you sure? The commenters on the selected answer seem to attest that it works ... – svidgen Mar 26 '13 at 21:33
@swidgen found my mistake, the answer there is correct. works by the magic of `dual`. +1 for you :) – scones Mar 26 '13 at 21:44
@scones Just tested on MySQL 5.1. It works. `insert into states (name) select 'Wisconsin' from dual where not exists (select name from states where name='Wisconsin');` – svidgen Mar 26 '13 at 21:45

Conditional INSERT INTO MySQL - WHERE NOT EXISTS

2 Answers2

Linked

Related