TSQL - update the table from yourself

Question

Table Element (4 columns simplified for 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 NULL with the appropriate values ​​from NULL based on Item . Thus, all subjects 100 will read 10.00, and both subjects 200 will read 25.00.

I feel it should be very simple, but I cannot figure out how to handle it.

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 several non-zero prices and you want to choose the 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 

Answer 2

If you have several prices for an item, you need to choose. This example selects the minimal one (it gets a little confused with the column name item , which matches the table name):

 UPDATE item i SET price = (SELECT MIN(price) FROM item WHERE item = i.item GROUP BY item)