CODEWITHSUNDEEP
sqlsql-server-2008
Joe Defill
Joe Defill

Reputation: 499

SQL update statement not updating all expected row

I have a select statement to grab all products

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

This returns 560 rows. But when I try updating all those rows, it says 225 rows updated instead of 560:

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

What am I doing wrong here?

Upvotes: 1

Views: 1289

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your original query has suppliers that have more than one product. Only one of these rows is updated in the second query.

You can run:

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

To see the difference.

I would recommend using exists for the update:

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

Upvotes: 1

Related Questions