SQL更新语句没有更新所有期望的行

Question

我有一个select语句来抓取所有产品

SELECT *  
FROM Products AS P
INNER JOIN Suppliers AS S ON S.product_id = P.product_id 

返回560行。但当我尝试更新所有这些行时，它显示更新了225行而不是560行:

UPDATE P
SET P.hasSupplier = 1
FROM Products AS P
INNER JOIN Suppliers AS S ON S.product_id = P.product_id 

我在这里做错了什么?

Answer 1

您的原始查询具有多个产品的供应商。在第二个查询中只更新其中一行。

你可以运行:

select count(*), count(distinct productid)
from suppliers;

看区别

我建议将exists用于update:

UPDATE P
SET P.hasSupplier = 1
FROM Products P
WHERE EXISTS (SELECT 1 FROM Suppliers S WHERE S.product_id = P.product_id);