I am trying to update a column value to 'yes' if the record exists in a subquery, and 'no' if it does not. The code and temp tables I have created are shown below.
if object_id('tempdb..#t1') is not null drop table #t1
CREATE TABLE #t1 (id nvarchar(max), astate varchar(16), code varchar(16), qual_code_flag varchar(8))
INSERT into #t1 VALUES
(100.1, 'CA', '0123', null),
(100.2, 'CA', '0124', null),
(100.3, 'PA', '0256', null),
(200.1, 'MA', '0452', null),
(300.1, 'MA', '0578', null),
(400.2, 'CA', '1111', null),
(500.1, 'CO', '1111', null);
if object_id('tempdb..#t3') is not null drop table #t3
CREATE TABLE #t3 (qual_code varchar(16), qual_state varchar(8))
INSERT into #t3 VALUES
('0123', ''),
('0124', ''),
('0256', ''),
('0452', ''),
('0578', ''),
('1111', 'CO');
update t1
set qual_code_flag = case when t1.* in (
select t1.*
from #t3 t3
inner join #t1 t1
on t1.code = t3.qual_code
and (t3.qual_state = t1.astate or t3.qual_state = '')
) then 'yes' else 'no' end
from #t1 t1
select * from #t1
This code is something I wish would work, but it throws an error because I am trying to update when t1.* in (subquery), which is not proper syntax. I know this query would not throw an error if I just had said, for example, when t1.code in (subquery), but what I need is an exact combination of code and state to be in the subquery. If you run what is inside the subquery, you will see that 6 out of the 7 records in #t1 get returned. These are all of the records which I want to update the flag to 'yes', and the record which does not exist in the subquery would have a flag value of 'no'. I think this should be a straightforward query but I am not coming up with the correct results yet.
No need to re-open the target table in the subquery: instead, you can use exists and a correlated subquery:
update #t1 t1
set qual_code_flag =
case when exists (
select 1
from #t3 t3
where t1.code = t3.qual_code and (t3.qual_state = t1.astate or t3.qual_state = '')
)
then 'yes'
else 'no'
end