您好，我有兩個表，其中表一是產品，表二是我想使用此 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 ( SELECT product.local_delivery, product.none' 附近使用正確的語法
uj5u.com熱心網友回復：
語法錯誤在 . 之前是一個逗號WHERE。
然后，您的更新陳述句會更新表中的所有行或不更新任何行，具體取決于類別 90 是否存在產品。
我想您寧愿更新類別 90 的所有產品。為了實作這一點，請將 product_to_category 與您正在更新的產品相關聯。如果我的假設是正確的，則查詢必須如下所示：
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
);
或者更簡單一點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);
uj5u.com熱心網友回復：
使用以下語法
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);
或遵循語法并洗掉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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/471297.html
上一篇：如何在php中格式化json回應