0

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

3
  • 5
    what haooens if yiou have yes nnd no?
    – nbk
    Mar 22, 2021 at 18:35
  • 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?
    – J.D.
    Mar 22, 2021 at 19:12
  • That wouldn't be possible in my table because one id can only have yes or no.
    – oddsumsum
    Mar 22, 2021 at 19:55

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.