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

Thomas G · Accepted Answer · 2019-04-15T12:40:37.633

2

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
;

edited Apr 15 '19 at 12:40

answered Apr 15 '19 at 12:35

Thomas G

9,886
7
28
41

Hi Thomas, my first SQL actually updates some columns, namely those which had null values. – Peter Frey Apr 15 '19 at 13:06
@PeterFrey the fact it works has probably something to do with NULLs. Have you tried my workaround for the second query? – Thomas G Apr 15 '19 at 14:28
Your solution gives me the same (positive) results as my SQL1. So it is okay here. I still do not get why my SQL1 works though. Any idea? – Peter Frey Apr 16 '19 at 12:17
At the end I think it was an issue with toad not being cloed for a while...I mark your answer to close the question – Peter Frey Apr 18 '19 at 14:07
@ThomasG adding the `WHERE t1.datasource is null` clause as you did - after the `WHEN` clause, that addition to the code did the work for me. I'm using this merge for update two values of a single record but, for avoid modify other records related, this additional `WHEN` clause is required - what a headache! – Marco Aurelio Fernandez Reyes Dec 19 '22 at 17:22

merge sql condition is null issue

1 Answers1