如果值不存在于同一表的同一行中，则插入值
如果值不存在于同一表的同一行中，则插入值
提问于 2020-01-24 11:24:40
我试着做这样的事
+----+------------+----------------+
| id | customerID |productID |
+----+------------+----------------+
| 1 | 123 | 1 |
+----+------------+----------------+
| 2 | 123 | 2 |
+----+------------+----------------+
| 3 | 456 | 2 |
+----+------------+----------------+一个客户可以有一些产品，但独特。另一个客户可以拥有相同的产品，但对他来说也是独一无二的。
我试着这样做：
INSERT INTO FavoriteProducts (customerID, productID)
SELECT * FROM (SELECT '123', '1') AS tmp
WHERE NOT EXISTS (
SELECT productID FROM FavoriteProducts WHERE customerID = '123'
) LIMIT 1;
SELECT * FROM FavoriteProducts;
+----+------------+----------------+
| id | customerID |productID |
+----+------------+----------------+
| 1 | 123 | 1 |
+----+------------+----------------+我再次尝试使用另一个prudoctID：
INSERT INTO FavoriteProducts (customerID, productID)
SELECT * FROM (SELECT '123', '2') AS tmp
WHERE NOT EXISTS (
SELECT productID FROM FavoriteProducts WHERE customerID = '123'
) LIMIT 1;
SELECT * FROM FavoriteProducts;
+----+------------+----------------+
| id | customerID |productID |
+----+------------+----------------+
| 1 | 123 | 1 |
+----+------------+----------------+但不起作用
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-01-24 11:28:22
您必须将NOT EXISTS上的查询与外部查询(也使用产品ID )链接起来：
INSERT INTO FavoriteProducts (customerID, productID)
SELECT *
FROM (SELECT '123', '2' AS pID) AS tmp
WHERE NOT EXISTS (
SELECT productID
FROM FavoriteProducts
WHERE customerID = '123' AND productID = tmp.pID
) LIMIT 1;您还可以使用UNIQUE约束来解决这个问题：
ALTER TABLE FavoriteProducts
ADD CONSTRAINT unique_customer_product UNIQUE (customerID, productID);..。通过使用该约束，您可以使用一个简单的INSERT
INSERT INTO FavoriteProducts (customerID, productID) VALUES
('123', '4');Stack Overflow用户
发布于 2020-01-24 11:38:27
您不需要子查询，但是必须在WHERE子句中使用productID的条件：
INSERT INTO FavoriteProducts (customerID, productID)
SELECT '123', '1'
WHERE NOT EXISTS (
SELECT productID
FROM FavoriteProducts
WHERE customerID = '123' AND productID = '1'
);见演示。
子查询很有用，因此不需要重复要在NOT EXISTS查询中插入的值，如下所示：
INSERT INTO FavoriteProducts (customerID, productID)
SELECT t.*
FROM (SELECT '123' customerID, '1' productID) t
WHERE NOT EXISTS (
SELECT productID
FROM FavoriteProducts
WHERE customerID = t.customerID AND productID = t.productID
);见演示。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59895488
复制相关文章
相似问题
腾讯云开发者
