Updating multiple columns sql

Question

I know that this is a question asked quite regularly, but still, would like to know on how to go for this.

I am trying to update three values, firstname, middlename,lastname from names table. The emplid is selected from a staging table. I'd like to know, on how to set NVL for this.

The query block will be like this:

update staging_table
set firstname, middlename,lastname =
            (select firstname,middlename,last 
             from names 
             where names.emplid = staging_table.emplid)

I want to check for the null values, as there are cases where middlename is null.

Part two: I have got a work around for this as of now, but still, would like to know a better technique as performance is getting affected.

As of now, I have used this update query:

update staging_table set first_name = nvl((select first_name from names where emplid = staging_table.emplid),' ')



update staging_table set middle_name = nvl((select middle_name from names where emplid = staging_table.emplid),' ')

Is there a better technique than this?

I have checked for coalesce function in oracle, but that checks for entire result, and if, any one of the parameter is null, only then, null value is returned. Here, I need to check the null values for individual fields.

Thanks for the guidance.

score 3 · Accepted Answer · edited May 23 '17 at 10:25

If there is a middlename that's null then it will be updated to NULL in your staging table. You're not doing a direct comparison of NULL to another value so there's nothing to "fail" on.

If you changed your UPDATE to the following then you might need to use NVL() or COALESCE() in order to update everything:

update staging_table
   set (firstname, middlename, lastname)
       = ( select firstname, middlename, last 
             from names 
            where names.emplid = staging_table.emplid
              and names.middlename = staging_table.middlename
                  )

You, however, don't have the last line and names.middlename = staging_table.middlename, so this doesn't apply.

It might be clearer if you did this as a MERGE instead:

merge into staging_table s
using ( select emplid, firstname, middlename, last
          from names
               ) n
   on (s.emplid = n.emplid)
 when matched then
      update
         set s.firstname = n.firstname
           , s.middlename = n.middlename
           , s.lastname = n.last

P.S., Don't use NVL(); it has a lot of potential pitfalls, COALESCE() is safer.

Thanks for the response, no, I am updating this staging table, by joining with the names table. I have not inserted the data for names and other fields in the table, and want to update it. I did try using nvl, but it failed. — vamosrafa, Nov 14 '13 at 08:46
I'm sorry I don't understand @vamosrafa... both what you've done and what I've said do what you want and you _don't_ need to use NVL. — Ben, Nov 14 '13 at 08:53
Just one small bit of information. When you intend to use `merge` statement, and the version of oracle is 11.2.0.2, and there are check constraints defined on updateable column(s), they will be ignored. Bug 9285259. — Nick Krasnov, Nov 14 '13 at 09:25

score 0 · Answer 2 · answered Nov 14 '13 at 09:15

0

May be this might be the answer:

update staging_table
set (firstname, middlename,lastname) =
    (select NVL(firstname , staging_table.firstname),
            NVL(middlename, staging_table.middlename),
            NVL(last      , staging_table.lastname)
       from names 
      where names.emplid = staging_table.emplid)

answered Nov 14 '13 at 09:15

eliuhy

81
1
5

Eliuhy: I am processing for individual fields now, as I am putting these update statement in an exec command. That has solved my problem. Thanks. – vamosrafa Nov 14 '13 at 11:32

Updating multiple columns sql

2 Answers2