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
Do not update null values in table with merge
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Do not update null values in table with merge
Hi,
Do you mean something like this?
Notice how the FN column in the NAMES table was not updated by the NULL value in the NAMES_TEMP table.
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Do not update null values in table with merge
Thanks Jim! So simple! That solution is perfect.