TSQL - Update table from itself

Question

Table Item (4 columns, simplified for the sake of clarity)

Record | Item | Price | Zone

Data

1      | 100  | 10.00 | A
2      | 100  | NULL  | B
3      | 100  | NULL  | C
4      | 200  | 25.00 | A
5      | 200  | NULL  | B

Trying to update the NULLs with the corresponding values from the non-NULLs based on Item. So all Item 100s would read 10.00 and both Item 200s would read 25.00.

I feel like this should be super easy, but can't figure out the self reference.

Thanks

Answer 1

here u go

UPDATE a
SET a.Price=b.Price
FROM  Item AS a
INNER JOIN Item AS b
ON a.item=b.item
WHERE a.Price is NULL AND b.price is NOT NULL

or if there are multiple Non-null prices and you want to choose maximum price.

 UPDATE a
 SET a.Price=(SELECT MAX(b.PRICE) FROM ITEM AS b WHERE b.Item=a.Item and b.Price is not null )
 FROM  Item AS a
 WHERE a.Price is NULL