merge sql condition is null issue

Question

I have two very similar SQL statements. On of them work and on not. The SQL error message seems to be misleading. Can you figure it out?

SQL 1 -- this works just fine

    Merge into   t1 
    Using ( 
        Select art_ref_nr, channel, some_value From s1 ) t2
    On ( t1.art_ref_nr = t2.art_ref_nr and t1.channel = t2.channel
        and ( t1.datasource is null
            or (t1.datasource = 'no such value' ) -- only null values will be found
        ))
    WHEN MATCHED THEN UPDATE SET
        t1.some_value = t2.some_value
    ,   t1.datasource = 'value 1'
    ;

SQL 2 -- this fails

    Merge into   t1 
    Using ( 
        Select art_ref_nr, channel, some_value From s1 ) t2
    On ( t1.art_ref_nr = t2.art_ref_nr and t1.channel = t2.channel
        and ( t1.datasource is null
       ))
    WHEN MATCHED THEN UPDATE SET
        t1.some_value = t2.some_value
    ,   t1.datasource = 'value 2'
    ;

SQL1 runs fine. SQL2 messages:

Columns referenced in the ON Clause cannot be updated: string Cause: LHS of UPDATE SET contains the columns referenced in the ON Clause

On the other side I reference the on-clause "datasource", in both SQLs, so the error message cannot be full truth.

It seems like the problem is that one time I only check for null value entries. But why does this affect the SQL logic?

Many greetings, Peter

Answer 1

My guess is that your first query doesn't produce an error because a matching row is never found.

For the second query, it has to do an UPDATE, but can't because you are refering the column to UPDATE into the ON clause.

To overcome this,try to move into a WHERE clause, the part of the ON clause refering the column(s) you are trying to UPDATE:

Merge into   t1 
Using ( 
    Select art_ref_nr, channel, some_value From s1 ) t2
On (t1.art_ref_nr = t2.art_ref_nr and t1.channel = t2.channel)
WHEN MATCHED THEN UPDATE SET
    t1.some_value = t2.some_value
,   t1.datasource = 'value 2'
WHERE t1.datasource is null
;