SQL Update table where Relation is inside other table

Question

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

removed the comma now (1093 - You can't specify target table 'product' for update in FROM clause) — Mehdi Gh, May 09 '22 at 05:57
Okay, syntax error fixed. I wonder, though, whether the update statement does what you want it to do. If there is a product with category 90 in the table, you'll update all rows. If not, you'll update no rows. — Thorsten Kettner, May 09 '22 at 05:59
No, need to update product list where the category id is 90 @ThorstenKettner — Mehdi Gh, May 09 '22 at 06:07
I've removed the SQL Server tag. Both error messages indicate you are using MySQL, not SQL Server, The error 1093 is discussed here: https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source/44971214#44971214, but in your case you shouldn't join to the products table in the subquery anyway. — Thorsten Kettner, May 09 '22 at 06:17

score 2 · Accepted Answer · answered May 09 '22 at 06:04

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

i used tad simpler with IN and it worked thanks a lot – Mehdi Gh May 09 '22 at 06:18 — Mehdi Gh, May 09 '22 at 06:18

RF1991 · Answer 2 · 2022-05-09T06:26:09.610

0

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

edited May 09 '22 at 06:26

answered May 09 '22 at 05:59

RF1991

2,037
4
8
17

1

Pointless selecting any columns in an EXISTS – Caius Jard May 09 '22 at 06:00
1

The reason why its "pointless" is because EXIST does not return columns rather than a "True" when the "Where" clause requirements are met in the sub query – Demeteor May 09 '22 at 06:22
Thanks for your mention. I have corrected it. – RF1991 May 09 '22 at 14:03

SQL Update table where Relation is inside other table

2 Answers2