SQL更新关系在其他表中的表

Question

您好，我有两个表，其中表1是针对产品的，表2是我想使用this SQL更新的类别，但不断出现语法错误

UPDATE product SET local_delivery = 0,none_local_delivery = 0,
WHERE EXISTS
(SELECT product.local_delivery,product.none_local_delivery 
FROM product
INNER JOIN product_to_category ON product.product_id = 
product_to_category.product_id WHERE
product_to_category.category_id = 90 )

1064-您的SQL语法有错误；检查与您的MySQL服务器版本对应的手册，在第4行的“WHERE EXISTS（SELECTproduct.local_delivery，product.none”附近使用正确的语法

Answer 1

使用以下语法

UPDATE p
SET    local_delivery = 0,
       none_local_delivery = 0
FROM   product AS p
WHERE  EXISTS (SELECT  1
               FROM   product P
                      INNER JOIN product_to_category PCT
                              ON P.product_id =
                                 PCT.product_id
               WHERE  PCT.category_id = 90);  

或遵循语法并删除存在的

UPDATE p
SET    local_delivery = 0,
       none_local_delivery = 0
FROM   product p
       INNER JOIN product_to_category PTC
               ON P.product_id = PTC.product_id
WHERE  product_to_category.category_id = 90  

Answer 2

语法错误是WHERE前面的逗号。

然后，update语句更新表中的所有行或无行，具体取决于类别90是否存在产品。

我想你宁愿更新90类的所有产品。为了实现这一点，请将产品类别与您正在更新的产品关联起来。如果我的假设是正确的，那么查询必须如下所示：

UPDATE product SET local_delivery = 0,none_local_delivery = 0
WHERE EXISTS
(
  SELECT NULL
  FROM product_to_category 
  WHERE product_to_category.product_id = product.product_id
  AND product_to_category.category_id = 90
);

或者用中的稍微简单一点：

UPDATE product SET local_delivery = 0,none_local_delivery = 0
WHERE product_id IN 
        (SELECT product_id FROM product_to_category WHERE category_id = 90);