SQL Update Statement Using If When

Question

I am trying to update one column if it is null with the values ​​of another column, only if this column is also not null. If it is zero, update column 1 with column 3

Here is an example:

  day_phone sec_phone alt_phone cell_phone
 555-555-1212 NULL NULL NULL
 NULL 555-555-1234 NULL NULL
 NULL NULL 555-555-4321 NULL
 NULL NULL NULL 555-555-5678
 NULL 555-123-4567 555-867-5309 NULL 

So what I want to do is update all the entries so that the day_phone method ends with a result, preferably the first one that it finds if there are entries in multiple columns. The end result should look like this:

  day_phone sec_phone alt_phone cell_phone
 555-555-1212 NULL NULL NULL
 555-555-1234 555-555-1234 NULL NULL
 555-555-4321 NULL 555-555-4321 NULL
 555-555-5678 NULL NULL 555-555-5678
 555-123-4567 555-123-4567 555-867-5309 NULL 

Answer 1

just use COALESCE

 UPDATE tableName SET day_phone = COALESCE(day_phone, sec_phone, alt_phone, cell_phone) 

• SQLFiddle Demo

if you want to update only if day_phone is null, then you can add an additional condition,

 UPDATE tableName SET day_phone = COALESCE(sec_phone, alt_phone, cell_phone) WHERE day_phone IS NULL 

this will make the update faster since only the selected rows are updated. Much faster if you have an index in the day_phone column.

Answer 2

The easiest way is a series of update statements.

 UPDATE table_name SET column1=column2 WHERE column1 IS NULL AND column2 IS NOT NULL 

repeat for column 3, 4, etc. Run in order of preference.

http://www.w3schools.com/sql/sql_null_values.asp

http://www.w3schools.com/sql/sql_update.asp