Update column based on entire record being present in subquery-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Can you just LEFT Join?

  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
    T1.qual_code_flag = 
    CASE
        WHEN T3.qual_code IS NULL  THEN 'No'
        ELSE 'Yes'
    END
FROM
    #t1 T1
LEFT JOIN
    #t3 T3 ON T1.code = T3.qual_code AND (T1.astate = T3.qual_state OR T3.qual_state = '')