Replace all column values with other values in the same column according to primary key

Question

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 "Unknowns" 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

• nbk: what haooens if yiou have yes nnd no?
• J.D.: To add on to nbk's question, which value do you expect to see when you have the same id three times, once with the unknown value for the applicable column, once with yes, and once with no?
• oddsumsum: That wouldn't be possible in my table because one id can only have yes or no.

Answer 1

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] <> '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<>fiddle here