Snowflake: unable to reference column in target table inside a case predicate for notMatchedClause i-CodePudding

It seems like I am able to reference a column the target table in a case predicate for a matchedClause in a MERGE command, but am unable to do so in a notMatchedClause.

For example I create two tables and insert some values to them as below.

create table test_tab_a (
    name string,
    something string
                  );

create table test_tab_b (
    name string,
    something string
                  );

insert into test_tab_a values ('a', 'b');
insert into test_tab_a values ('c', 'z');
insert into test_tab_b values ('a', 'c');
insert into test_tab_b values ('c', 'z');

Then run a merge command as below and works just fine.

merge into public.test_tab_a as target
using (
    select * from public.test_tab_b
    ) src
on target.name = src.name
when matched and target.SOMETHING = src.something then delete;

However when I run a command using a not matched clause, I get an invalid identifier error.

merge into public.test_tab_a as target
using (
    select * from public.test_tab_b
    ) src
on target.name = src.name
when not matched and  b.SOMETHING != a.something then insert values (name, something);

Why is the case_predicate evaluated differently depending on the type of clause?

CodePudding user response：

Interesting find, I get the same thing, do not see anything in the documentation mentioning it is not available. So, I would recommend submitting a support case with Snowflake.

As a workaround, you could add logic to your subselect by joining to test_tab_a, like this:

merge into test_tab_a as a
using (
    select test_tab_b.name, test_tab_b.something
    from test_tab_b sub_b
    inner join test_tab_a sub_a on sub_b.name = sub_a.name
    where sub_b.SOMETHING != sub_a.something
    ) b
on a.name = b.name
when not matched then insert values (name, something);

CodePudding user response：

Adding some more detail. I also checked other rdbms and haven't seen specific documentation if they support this behavior either (specifically WHEN NOT MATCHED AND condition referencing target table). Is this query pattern coming from another rdbms?

Are there other pieces of this Merge that have been left off for simplicity? It seems like an Insert/Left Join is more useful than a Merge in this case.

insert into public.test_tab_a
select b.name,b.something from public.test_tab_b b
left join public.test_tab_a a
on a.name = b.name 
where a.something != b.something

I will raise a Snowflake feature request for this use-case.