Compare table and update flag

Sarath 106 Reputation points
2020-11-21T10:00:18.4+00:00

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

41575-table1.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,574 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.7K Reputation points MVP
    2020-11-21T10:32:08.647+00:00
     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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Visakh 211 Reputation points
    2020-11-22T06:00:05.997+00:00

    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

    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points
    2020-11-23T03:11:00.16+00:00

    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  
    

    41700-image.png

    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

    0 comments No comments