How to replace NULL values in a row, with other values in that same row in SQL Query-CodePudding

So I'm querying something like the following, however, some of my rows are NULL and I want the query to replace the values where NULL to a specific different value in the same row:

SELECT * FROM table      >>>

table
"ID"    "First Name"    "Last Name"    "Default - First Name"   "Default - Last Name"
"1111"  "Bill"          "Jones"        "FN Name 1"              "LN Name 1"
"2222"   NULL            NULL          "FN Name 2"              "LN Name 2"
"3333"  "Emma"          "Jean"         "FN Name 3"              "LN Name 3"

So I want my final Query result to be this:

Final Query Result
"ID"    "First Name"    "Last Name"    "Default - First Name"   "Default - Last Name"
"1111"  "Bill"          "Jones"        "FN Name 1"              "LN Name 1"
"2222"  "FN Name 2"     "LN Name 2"    "FN Name 2"              "LN Name 2"
"3333"  "Emma"          "Jean"         "FN Name 3"              "LN Name 3"

So I want to replace the NULL values with the values in the same row in the default First and Last name columns.

Any ideas on how I can query this?

CodePudding user response：

you can use the coalesce function to specify the first non null value
in your case the code will always look for first_name column value if the value is null it will then take the value from the default_first_name column
you can change the logic based on your needs
similarly you can pass the values to be taken if the default_first_name or default_last_name has nulls

select
*,
coalesce(first_name, default_first_name) as new_first_name,
coalesce(last_name, default_last_name) as new_last_name,
default_first_name,
default_last_name
from table

select
*,
coalesce(first_name, default_first_name) as new_first_name,
coalesce(last_name, default_last_name) as new_last_name,
coalesce(default_first_name, 'NO VALUE FOUND') as new_default_first_name,
default_last_name, 'THIS IS NULL') as new_default_last_name
from table

CodePudding user response：

SELECT id,
        ifnull(first_name,default_first_name) as first_name, 
        ifnull(last_name,default_last_name) as last_name from mytable

If you are certain about the column name, can always use ifnull for MySQL, isnull fr MSSQL Server. this functions checks if firsat parameter is of null value, then returns the "value" of secon prameter, taken as column.

Hope this helps :)

CodePudding user response：

Using coalesce(col1,col2) it will pick the first non null value from the list of columns you give it, so use coalesce(First Name, Default - First Name) to set new value of the column to the other columns value.

UPDATE TABLE SET `First Name` = coalesce(`First Name`, `Default - First Name`),
                 `Last Name`  = coalesce(`Last Name`, `Default - Last Name`)
WHERE `First Name` IS NULL 
OR `Last Name` IS NULL