Updating Multiple Column with Staging table avoiding Null Values in Staging Table

Question

I want to update multiple column in Table2 table from Table1 table. Staging tables may have null value in it. I just want to update data which has value in the Table1 table - not NULL values.

Please advice

e.g

Table 1                             Table 2
ID    col11   col2     col3         ID1      col1       col2       col3
3     test    xxxx     (null)       3        (null)   jjj        ffffff
5     (null)  yyyyy    zzzzzz       5        dddd     (null)     lllllllll

Output

ID      col1      col2      col3
3        test     xxxx      ffffff
5        dddd     yyyyy     zzzzzz

It should not update value in Table 2 will null if value in Table 1 is null.

My approach right now is

UPDATE  Table2 
SET ( col1 ,col2,col3)  
      =  (SELECT NVL(col1 ,col11), NVL(col2,col22), NVL(col3,col33)  
          FROM Test1  
          WHERE ID = ID1 )

score 1 · Answer 1 · edited May 23 '17 at 12:27

Update each column with COALESCE of the staging value and the existing target value, supposing the columns are the same or similar types.

This is no different from NVL in this simple case, although there are some differences worth knowing about, especially portability, and in more complex cases, performance.

While it may seem wasteful to you that each column is technically updated, it is not so. Once you update a database row, the cost is incurred. You could make a much more complicated query using dynamic SQL that would do multiple updates, one for each possible column combination, but that solution would be drammatically slower, and obviously, harder to maintain.

Updating Multiple Column with Staging table avoiding Null Values in Staging Table

1 Answers1