我试图更新1列,这是基于第二个表中的categories\u id是否等于90,但得到一个未知的列错误。
以下是我的sql:
UPDATE products SET qty='20'
WHERE products.products_id = products_to_categories.products_id AND products_to_categories.categories_id = '90'
我得到的错误是
“where子句”中的“products_to_categories.products_id”列未知
最佳答案
在SQL中,必须使用from
(或update
或delete
)语句引入表名。你可能是说:
UPDATE products
SET qty = '20'
WHERE exists (select 1
from products_to_categories ptc
where ptc.products_id = products.products_id AND
ptc.categories_id = '90'
);
另一种方法是使用join:
UPDATE products p join
products_to_categories ptc
on ptc.products_id = products.products_id AND
ptc.categories_id = '90'
SET p.qty = '20';
如果有多个匹配项,则效果更好。
关于mysql - 更新语句给出未知列错误,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18582949/