Replace null with known value with same id

问题:

How can I do that in a optimal way?

TABLE

ID FLAG VALUE
----------------------------
1   Y   52
1   N   NULL
2   Y   51
3   N   54

OUTPUT

ID FLAG VALUE
----------------------------
1   Y   52
1   N   52
2   Y   51
3   N   54

回答1:

UPDATE 
  theTable
SET 
  value = (SELECT MAX(value) FROM theTable i WHERE i.id = theTable.id)
WHERE
  value IS NULL

回答2:

you can do it in two ways

SELECT Id,
       Flag, 
       MAX(Value) OVER(PARTITION BY Id) CValue
FROM   Table_1

or

select X.Id, X.Flag,
case when ISNULL(X.Value,0) = 0 then 
(select MAX(A.Value) from Table_1 A where A.Id = X.Id)
 else X.Value end
from Table_1 X