MYSQL更新连接为空

Question

我想知道如果 product_id 没有在其他表中分配任何 category_id，我可以将产品状态更改为 0 吗？

+------------+----------+  +------------+-------------+
| Product_id |  Status  |  | Product_id | cateogry_id |
+------------+----------+  +------------+-------------+
|      1     |     1    |  |     1      |      10     |
|      2     |     1    |  |     3      |      20     |
|      3     |     1    |  +------------+-------------+
+------------+----------+

结果我需要 product_id = 2 没有类别状态为 0。有一个 MySQL 查询吗？

Answer 1

首先很简单，您需要获取表中不存在的行..

table1 是状态表
table2是类别id的表

           table1                     table2
+------------+----------+  +------------+-------------+
| Product_id |  Status  |  | Product_id | cateogry_id |
+------------+----------+  +------------+-------------+
|      1     |     1    |  |     1      |      10     |
|      2     |     1    |  |     3      |      20     |
|      3     |     1    |  +------------+-------------+
+------------+----------+

所以现在运行这个查询来获取没有 category_id 的行

SELECT product_id 
FROM table1 t
LEFT JOIN table2 t2 ON t2.product_id = t.product_id
WHERE t2.product_id IS NULL

现在像这样更新表1

UPDATE table1 t,
(   SELECT product_id 
    FROM table1 t
    LEFT JOIN table2 t2 ON t2.product_id = t.product_id
    WHERE t2.product_id IS NULL
) t1
SET t.status = 0
WHERE t1.product_id = t.product_id

DEMO