我有一个包含以下列的表： id , store , category_id 以及 option_id . 此表的内容通过web钩子从其他服务器上的原始表更新。一旦收到的内容，我将需要检查，看看哪些行需要删除，哪些需要插入。
为了简单起见，让我们假设我从web钩子收到的category和option id元组是（1,1）和（1,2），并且数据库已经包含（1,1）和（1,3）。所以（1,3）需要删除，（1,2）需要插入。
我可以这样做：
DELETE FROM store_category_options
WHERE store=1 AND (category_id, option_id) NOT IN ((1,1), (1,2));但是，插入需要两个查询，一个用于检索数据库中已有的值
SELECT category_id, option_id FROM store_category_options WHERE store=1在mysql之外计算出差值后，再插入一个：
INSERT INTO store_category_option (category_id, option_id) VALUES (1,2)我想知道是否有办法用一个查询而不是两个查询来进行插入。
1条答案
按热度按时间s3fp2yjn1#
最后我自己想出来了。我们可以从将webhook数据转换成一个派生表开始
WHERE NOT IN```SELECT 1 as store_category_id, 1 as store_option_id UNION SELECT 1, 2;
+-------------------+-----------------+
| store_category_id | store_option_id |
+-------------------+-----------------+
| 1 | 1 |
| 1 | 2 |
+-------------------+-----------------+
2 rows in set (0.00 sec)
SELECT store_category_id, store_option_id FROM (
SELECT 1 as store_category_id, 1 as store_option_id
UNION SELECT 1, 2
) AS vt WHERE store_option_id = 1;
+-------------------+-----------------+
| store_category_id | store_option_id |
+-------------------+-----------------+
| 1 | 1 |
+-------------------+-----------------+
1 row in set (0.00 sec)
SELECT store_category_id, store_option_id FROM (
SELECT 1 as store_category_id, 1 as store_option_id
UNION SELECT 1, 2
) AS vt WHERE (category_id, option_id) NOT IN (
SELECT store_category_id, store_option_id
FROM store_category_options
);
+-------------------+-----------------+
| store_category_id | store_option_id |
+-------------------+-----------------+
| 1 | 2 |
+-------------------+-----------------+
1 row in set (0.00 sec)
INSERT INTO store_category_options
(store_category_id, store_option_id)
SELECT store_category_id, store_option_id FROM (
SELECT 1 as store_category_id, 1 as store_option_id
UNION SELECT 1, 2
) AS vt WHERE (store_category_id, store_option_id) NOT IN (
SELECT store_category_id, store_option_id
FROM store_category_options
);