有条件地插入MySQL-不存在 [英] Conditional INSERT INTO MySQL - WHERE NOT EXISTS

问题描述

当客户没有购买产品的记录时，我正在尝试执行INSERT INTO查询.

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

我尝试了以下SQL，但似乎无法正常工作.

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')";

我收到的错误如下:

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以获取正确的语法，以在第3行的不存在(选择购买的产品"中使用)附近

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

任何建议将不胜感激！

推荐答案

如果可能，请向表中添加UNIQUE约束.像...

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

如果这不是适合您的解决方案，请在此处使用选择的答案: MySQL条件插入

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

答案很简单:

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

这篇关于有条件地插入MySQL-不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！