SQl to Update columns when the fields are null

Question

I have a SQL question,I am relatively new to SQL so please don't mind if this too naive.I am working on updating values from one table to another in ORACLE.

Table Person

PersonID    BirthPlace
---------------------------
1           Madison,WI,USA        
2            Chicago,IL              
3            Houston,TX,USA            
4            Madison,WI,USA          
5            Madison,WI,USA           
6            Houston,TX,USA            
7            NULL

Table PersonProfile

PersonID  CITY             STATE       COUNTRY
-------------------------------------------------
1         Madison           WI          USA 
2         Chicago           IL          NULL  
3            NULL           NULL        NULL 
4            NULL           WI          NULL 
5            NULL           NULL        USA   
6            HOUSTON        NULL        NULL  
7            NULL           NULL        NULL

I need to update Table Person Profile with values from Table Person and only need to update the columns in Table Person Profile when they have null values and if both the tables have null value then I need to put 'Unknown'.

I can write separate update statements to update the values in each columns like for updating city :

Update PersonProfile PF

   SET PF.CITY= (SELECT 
                 CASE
                  WHEN PP.CITY LIKE '%MADISON%'
                        THEN 'MADISON'
                  WHEN PP.CITY LIKE '% Houston%'    
                        THEN 'HOUSTON'
                  WHEN PP.CITY LIKE '% CHICAGO%'    
                        THEN 'CHICAGO'
                  ELSE
                       'UNKNOWN'
                  END AS CITY
                            FROM PERSON PP
                       WHERE PF.PERSONID=PP.PERSON.ID
                       AND   PF.CITY IS NULL)

and similar queries for updating the state,and country.

What my question is that is there any way I can write a single update statement for updating all three columns instead of updating them one by one? and also instead of using CASE statements if I can use the like operator in a decode function?

If `Person` and `PersonProfile` share identical unique keys, why are they in separate tables? — Matt, Aug 08 '14 at 14:48
They are in two separate databases,So I need to do the conversion from one to another and shutdown the old one — Dhrubojyoti Bhattacharjee, Aug 08 '14 at 15:50

score 1 · Answer 1 · answered Aug 08 '14 at 14:56

Yes, either update a tuple like:

set (city, state, country) = (select case when pp.city like ... end as city
                                   , case when ...              end as state
                                   , case ...                   end as country)

bu I suspect that it would be better to create a table function (I assume these exists in Oracle) that splits the string in three columns using , as a separator and then use merge (assuming this als exists in Oracle) to update. Something like:

merge into t2
using t1 (
   select personid, table(split(birthplace))
   from t1
) x (PersonID, CITY, STATE, COUNTRY)
    on t2.person_id = x.personid
when matched 
    set t2.city = case when t2.city is null then x.city else t2.city end
      , t2.state = ...
  , ...

Gary_W · Accepted Answer · 2015-07-28T14:36:27.860

Use a multi-column update and regular expressions to parse the birthplace data on a comma.

update PERSONPROFILE pp
set (city, state, country) = (
  select nvl(REGEXP_SUBSTR (BIRTHPLACE, '[^,]+', 1, 1), 'UNKNOWN'), -- city
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '[^,]+', 1, 2), 'UNKNOWN'), -- state
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '[^,]+', 1, 3), 'UNKNOWN')  -- country
  from PERSON p
  where p.id = pp.id);

The regex matches 0 or more characters that do not match a comma, the first occurrence for city, 2nd occurrence for state, etc. Wrap it in NVL to set to "UNKNOWN" if the occurance is NULL.

UPDATE: I have since learned the regex expression '[^,]+' should be avoided as it fails when there are NULL elements in the list and you are selecting that element or one after it. Use this instead as it allows for NULLs:

update PERSONPROFILE pp
set (city, state, country) = (
  select nvl(REGEXP_SUBSTR (BIRTHPLACE, '([^,]*)(,|$)', 1, 1, NULL, 1)), 'UNKNOWN'), -- city
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '([^,]*)(,|$)', 1, 2, NULL, 1)), 'UNKNOWN'), -- state
         nvl(REGEXP_SUBSTR (BIRTHPLACE, '([^,]*)(,|$)', 1, 3, NULL, 1)), 'UNKNOWN')  -- country
  from PERSON p
  where p.id = pp.id);

For more info see this post: Split comma seperated values to columns

score 0 · Answer 3 · edited May 23 '17 at 12:14

You need to review your design - there are several corner cases when you deal with postal addresses and curating user entered values (which I assume you are doing).

This has already been discussed here at SO:

SQL Database Design Best Practice (Addresses) SQL Database Design Best Practice (Addresses)
How to best design address locations in any SQL Database? How to best design address locations in any SQL Database?
Best practices for storing postal addresses in a database (RDBMS)? Best practices for storing postal addresses in a database (RDBMS)?
Best Practice / Standard for storing an Address in a SQL Database Best Practice / Standard for storing an Address in a SQL Database

SQl to Update columns when the fields are null

3 Answers3