mysql - 更新语句给出未知列错误

Question

我正在尝试根据第二个表中的 categories_id 是否等于 90 来更新 1 列，但出现未知列错误。

这是我的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”

Answer 1

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

另一种方法是使用连接:

UPDATE products p join
       products_to_categories ptc
       on  ptc.products_id = products.products_id AND
           ptc.categories_id = '90'
    SET p.qty = '20';

如果有多个匹配项，这种方法效果会更好。