MERGE - conditional "WHEN MATCHED THEN UPDATE" and update the column

Question

I have three snowflake tables(TEST1, TEST2, CONTROL) as below.

TEST1

create OR REPLACE table TEST1 (
  id varchar(100), 
  name varchar(100),"DEV_COMMON_DATA"."STG_OD_CMS"."CONTROL"
  address VARCHAR(64)
);    

INSERT INTO TEST1 values (100, 'ABC', '345 thg');
INSERT INTO TEST1 values (200, 'XYZ', null);
INSERT INTO TEST1 values (300, 'VBN', null);

TEST2

create OR REPLACE table TEST2 (
  id varchar(100), 
  name varchar(100),
  address VARCHAR(64),
  flag VARCHAR(64)
);    

INSERT INTO TEST2 values (100, 'ABC', '345 thg', null);
INSERT INTO TEST2 values (200, 'FDS', '346 dfg', null);

CONTROL

create OR REPLACE table CONTROL (
  KEY_COLUMNS VARCHAR,
  lookup_columns VARCHAR,
  TABLE_NAME_SOURCE VARCHAR,
  TABLE_NAME_TARGET VARCHAR
);    

INSERT INTO CONTROL values ('id,name, address', 'id,name', 'TEST2','TEST1');

I want to incorporate insert and update to 'TEST2' based on 'TEST1' table.

If records does not exist in TEST2, then the stored procedure should insert rows into TEST2 from TEST1 and update column 'flag' in TEST2 as 'INSERT'

If there is any update to TEST1, it should be captured in TEST2.

My stored procedure looks like below. I have declared variables which come from control table. My script works completely fine.

For update, the condition needs to be (if id matches and if hash of all the columns specified in lookup_columns in TEST2 doesnot match with lookup_columns in TEST1 ), it should update all columns specified in KEY_COLUMNS from TEST1 to TEST2 and set the flag is 'update'.

Something is wrong with my 'WHEN MATCHED' condition

CREATE OR REPLACE PROCEDURE SP() 
    RETURNS string
    LANGUAGE SQL
    AS
    $$  
    DECLARE
           source_tbl STRING := (select TABLE_NAME_SOURCE from CONTROL);
           source_columns STRING;
           target_columns STRING;
           update_sets STRING;
           lookups_column STRING;
           query1 STRING;
     BEGIN
          SELECT KEY_COLUMNS INTO :source_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT KEY_COLUMNS INTO :target_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT LOOKUP_COLUMNS INTO :lookups_column FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT listagg(' A.'||s.value||'=B.'||s.value, ',') INTO :update_sets FROM CONTROL, TABLE(SPLIT_TO_TABLE(KEY_COLUMNS, ',')) s WHERE TABLE_NAME_SOURCE = :source_tbl;;
          QUERY1 := 'MERGE INTO TEST2 AS A 
                    USING (
                      select '|| :source_columns ||' from TEST1) AS B 
                    ON A.ID=B.ID 
                    WHEN NOT MATCHED THEN INSERT ('|| :target_columns ||', FLAG) 
                    values ('|| :source_columns ||', ''INSERT'')
                    WHEN MATCHED AND ((select SHA2_HEX(CONCAT_WS(\'|\','|| :lookups_column ||')) from TEST1) <> ((select SHA2_HEX(CONCAT_WS(\'|\','|| :lookups_column ||')) from TEST2)) THEN 
                    update set '|| :update_sets ||', FLAG = ''UPDATE'';';
           EXECUTE IMMEDIATE :QUERY1;

           RETURN :QUERY1;
      END;
      $$;
      
call SP();

Expected TEST2 Output

Answer 1

You need to get rid of the "FROM TEST1"/"FROM TEST2" in your WHEN MATCHED statement. That will cause it to go out to the whole table, rather than referring to the specific row that you want to compare.

Something like this:

WHEN MATCHED AND (SHA2_HEX(CONCAT_WS(\'|\','|| :lookups_column ||'))) <> (SHA2_HEX(CONCAT_WS(\'|\','|| :lookups_column ||'))) THEN 
                    

Though you'll probably need a version of lookup_columns with a.column1, a.column2 and another with b.column1, b.column2, similar to your update clause.

Answer 2

It worked with the below update:

CREATE OR REPLACE PROCEDURE SP() 
    RETURNS string
    LANGUAGE SQL
    AS
    $$  
    DECLARE
           source_tbl STRING := (select TABLE_NAME_SOURCE from CONTROL);
           source_columns STRING;
           target_columns STRING;
           update_sets STRING;
           lookups_column_source STRING;
           lookups_column_target STRING;
           query1 STRING;
     BEGIN
          SELECT KEY_COLUMNS INTO :source_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT KEY_COLUMNS INTO :target_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT listagg(' A.'||s.value||'=B.'||s.value, ',') INTO :update_sets FROM CONTROL, TABLE(SPLIT_TO_TABLE(KEY_COLUMNS, ',')) s WHERE TABLE_NAME_SOURCE = :source_tbl;;
          SELECT listagg(' A.'||s.value, ',') INTO :lookups_column_source FROM CONTROL, TABLE(SPLIT_TO_TABLE(LOOKUP_COLUMNS, ',')) s WHERE TABLE_NAME_SOURCE = :source_tbl;;
          SELECT listagg(' B.'||s.value, ',') INTO :lookups_column_target FROM CONTROL, TABLE(SPLIT_TO_TABLE(LOOKUP_COLUMNS, ',')) s WHERE TABLE_NAME_SOURCE = :source_tbl;;
          QUERY1 := 'MERGE INTO TEST2 AS A 
                    USING (
                      select '|| :source_columns ||' from TEST1) AS B 
                    ON A.ID=B.ID 
                    WHEN NOT MATCHED THEN INSERT ('|| :target_columns ||', FLAG) 
                    values ('|| :source_columns ||', ''INSERT'')
                    WHEN MATCHED AND (SHA2_HEX(CONCAT_WS(\'|\','|| :lookups_column_source ||'))) <> (SHA2_HEX(CONCAT_WS(\'|\','|| :lookups_column_target ||'))) THEN  
                    update set '|| :update_sets ||', FLAG = ''UPDATE'';';
           EXECUTE IMMEDIATE :QUERY1;

           RETURN :QUERY1;
      END;
      $$;
      
call SP();