Replace all column values with other values in the same column according to primary key I'm querying the following table:




id
value
applicable




id1
5
Unknown


id1
5
Unknown


id1
6
Yes


id 2
5
Unknown


id 2
5
No


id 2
3
Unknown




I would like to replace all &quot;Unknowns&quot; in the applicable column, with the non-unknown values pertaining to each id.
Ideally, I would like to see this:




id
value
applicable




id1
5
Yes


id1
5
yes


id1
6
Yes


id 2
5
No


id 2
5
No


id 2
3
No




Sorry if this a very easy question, I'm new to this!
Thank you
This expects one one value besides of unknown, if this si not the case you must choose another aggregation function


CREATE TABLE table1
    ([id] varchar(4), [value] int, [applicable] varchar(7))
;
    
INSERT INTO table1
    ([id], [value], [applicable])
VALUES
    ('id1', 5, 'Unknown'),
    ('id1', 5, 'Unknown'),
    ('id1', 6, 'Yes'),
    ('id 2', 5, 'Unknown'),
    ('id 2', 5, 'No'),
    ('id 2', 3, 'Unknown')
;
GO




UPDATE table1
SET    table1.[applicable]  = t1.[applicable] 
FROM   table1
       INNER JOIN (SELECT [id],MIN([applicable]) as [applicable] FROM table1  WHERE  [applicable] &lt;&gt; 'Unknown' GROUP BY [id]) t1
         ON table1.id = t1.id



GO




SELECT * FROM    table1
GO


id   | value | applicable
:--- | ----: | :---------
id1  |     5 | Yes       
id1  |     5 | Yes       
id1  |     6 | Yes       
id 2 |     5 | No        
id 2 |     5 | No        
id 2 |     3 | No        


db&lt;&gt;fiddle here