Compare table and update flag

Question

Hi Team,

I have 3 tables as shown below
I am using MS flow - I get the ID in flow and fetch the Code for that ID in Table 1 (Eg: for 101 ID the code is 998877)
Now need to compare that code (998877) in Table 2
If that code exists in table 2 need to update the Flag field as 1 in Table 3 else flag 0.
Please assist me

Thank - Sarath

Accepted Answer

 UPDATE Table3
 SET    Flag = 1
 FROM   Table3 t3
 WHERE  EXISTS (SELECT *
                FROM  Table2 t2
                JOIN  Table1 t1 ON t2.Code = t1.Code
                WHERE t1.ID = t3.ID)

Had you posted your data as CREATE TABLE + INSERT statements, I would have tested my solution. Now it is untested.

Answer

Here's a small repro of your scenario

CREATE TABLE t1
(
ID int,
Code int
)
INSERT t1
VALUES(101,119887),(102,322124)

CREATE TABLE t2
(
Code int
)
INSERT t2
VALUES(119887)

CREATE TABLE t3
(
ID int,
Flag bit
)
INSERT t3 (ID) VALUES (101),(102),(103)


MERGE t3 AS t
USING (SELECT t1.ID
FROM t2
JOIN t1
ON t1.Code = t2.Code
) AS s
ON s.ID = t.ID
WHEN MATCHED
THEN UPDATE  SET Flag = 1
WHEN NOT MATCHED BY SOURCE
THEN
 UPDATE  SET Flag = 0;

 SELECT * FROM t3

It has all cases covered case where Code is in table t2, The flag in t3 is set to 1 and 0 for missing cases i.e. when Code is not in t2 or if ID details itself is missing from t1

Answer

Hi @Sarath ,

Please refer to:

    declare @Table1 table(ID int,Code int)  
    insert into @Table1  
    values(101,998877),(102,887766),(103,776655),(104,665544),(105,554433),(106,332211)  
    declare @Table2 table(Code int)  
    insert into @Table2 values(998877),(887766),(776655),(1000),(2000),(3000)  
    declare @Table3 table(ID int,Flag int)  
    insert into @Table3 values(101,null),(102,null),(103,null),(104,null),(105,null),(106,null)  
      
    select * from @Table1  
    select * from @Table2  
    select * from @Table3  
      
    update @Table3  
    set Flag=case when ID in(select ID   
                from @Table1   
                where Code   
     in(select Code from @Table2)) then 1 else 0 end   
    from @Table3  
      
    select * from @Table3

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Share via

Compare table and update flag

2 additional answers