当客户没有购买产品的记录时,我正在尝试执行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
发布评论