Update column with value from two potential columns

I have 3 different columns for phone numbers that I need to fix in my table. My goal is to replace phone (if empty or null) with the value of either phone2 or phone 3. I don’t care which one as long as it’s a phone number. I know how to do this with one column (see code below), but cannot figure out how to use an OR statement for the 3rd column. Query and data is below.

Original query

update test.10_master 
set Phone = phone2
WHERE phone IS NULL OR phone = ''

Original data

id    phone       phone2      phone3
1     1111111111
2                 2222222222        
3                             3333333333
4                 2222222222  3333333333      
5     1111111111  2222222222

What I want to see

id    phone       phone2      phone3
1     1111111111
2     2222222222  2222222222        
3     3333333333              3333333333
4     2222222222  2222222222  3333333333      
5     1111111111  2222222222

I’d really appreciate any suggestions. I really want to avoid having to run two different queries

Answer

To deal with null values, you can use coalesce():

update master
set phone = coalesce(phone2, phone3)
where phone is null

This updates phones that are null with phone2 or phone3 (the first non-null column wins).

If you want to avoid empty strings as well as nulls:

update master
set phone = case when phone2 is null or phone2 = '' then phone3 else phone2 end
where phone is null or phone = ''

Advertisement

Answer