SQL Update table where Relation is inside other table-CodePudding

hello I have two Table where table number one is for product and table two is the category i want to update using the this SQL but keep getting syntax error

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 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE EXISTS ( SELECT product.local_delivery, product.none' at line 4

CodePudding user response：

The syntax error is a comma right before WHERE.

Your update statement then updates either all or no rows in the table, depending on whether a product exists for category 90.

I suppose you rather want to update all produts of category 90 instead. In order to achieve that, relate the product_to_category to the product you are updating. If my assumtion is correct, the query will have to look like this:

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
);

Or a tad simpler with IN:

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);

CodePudding user response：

use following syntax

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);

Or following syntax and remove where exist

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