Mysql通过加入另一个表来更新表

Question

我有下面的表格，

表格产品,

product_id | name 
-----------------------
      1    | product 1 
      2    | Product 2
      3    | Product 3     
      4    | product 4    
      5    | product 5  
      .    | ....
      .    | ....

表 product_to_zone,

product_id | zone_id 
--------------------
     1     | 10 
     1     | 11
     2     | 10    
     2     | 11   

有什么方法可以将 zone_id 10,11 添加为 product_to_zone 表中所有未分配产品的默认值(即 product_id 3,4,5)

Answer 1

可能是这样的:-

SELECT a.product_id, a.name, b.zone_id
FROM products a
INNER JOIN product_to_zone b
ON a.product_id = b.product_id
UNION
SELECT a.product_id, a.name, 10
FROM products a
LEFT OUTER JOIN product_to_zone b
ON a.product_id = b.product_id
WHERE b.product_id IS NULL
UNION
SELECT a.product_id, a.name, 11
FROM products a
LEFT OUTER JOIN product_to_zone b
ON a.product_id = b.product_id
WHERE b.product_id IS NULL

如果你只想添加未分配的，那么像这样:-

INSERT INTO product_to_zone (product_id, zone_id )
SELECT a.product_id, 10
FROM products a
LEFT OUTER JOIN product_to_zone b
ON a.product_id = b.product_id
WHERE b.product_id IS NULL
UNION
SELECT a.product_id, 11
FROM products a
LEFT OUTER JOIN product_to_zone b
ON a.product_id = b.product_id
WHERE b.product_id IS NULL