Do not update null values in table with merge

Moderator: NorbertKrupa

Post Reply
User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Do not update null values in table with merge

Post by piglet » Wed Sep 11, 2013 7:17 pm

Howdy,

I need to write a merge statement for a table column where the source could be null. If it is null, I don't want that column updated but I want the rest of the columns updated. Is there an option of the merge command that I can use to selectively update a column?

If you need more info. please let me know!

Thank you

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Do not update null values in table with merge

Post by JimKnicely » Wed Sep 11, 2013 7:56 pm

Hi,

Do you mean something like this?

Code: Select all

dbadmin=> select * from names;
 mrn | fn  |   ln
-----+-----+---------
   1 | Jim | Knisely
(1 row)

dbadmin=> select * from names_temp;
 mrn | fn |   ln
-----+----+---------
   1 |    | Knicely
(1 row)

Code: Select all

dbadmin=> merge into names using names_temp on names_temp.mrn = names.mrn
dbadmin->   when matched then update set
dbadmin->     fn = nvl(names_temp.fn, names.fn),
dbadmin->     ln = names_temp.ln;
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from names;
 mrn | fn  |   ln
-----+-----+---------
   1 | Jim | Knicely
(1 row)
Notice how the FN column in the NAMES table was not updated by the NULL value in the NAMES_TEMP table.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Re: Do not update null values in table with merge

Post by piglet » Thu Sep 12, 2013 3:20 pm

Thanks Jim! So simple! That solution is perfect.

Post Reply

Return to “New to Vertica SQL”