我试图更新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(或updatedelete)语句引入表名。你可能是说:

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/

10-10 01:46