1

I have this old_table.

ID:    First_name:    Last_name:    Age:
1      Christian      Johnson       25
2      John           Christiansen  21
3      <NULL>         Peterson      23

and this new_table I just created, by this piece of code:

CREATE TABLE new_table (ID integer, name text, age text);

INSERT INTO new_table (ID, name, age) SELECT ID, First_name, age FROM old_table;

Which returned:

ID    Name:        Age:
1     Christian    25
2     John         21
3     <NULL>       23

But I would love my code to insert the lastname, if it encounters a NULL-value. So in pseudo-code;

(...) SELECT ID, First_name IF NULL last_name, age FROM old_table;

The IF NULL last_name doesn't work.

Karl Kristiansen
  • 117
  • 9
  • someone already asked the similar problem, possibly [this](http://stackoverflow.com/questions/5951157/if-in-select-statement-choose-output-value-based-on-column-values) may help! – Saubar May 06 '15 at 16:45

2 Answers2

2

You are looking for COALESCE(), which returns the first not-null values it encounters:

INSERT INTO new_table (ID, name, age) 
    SELECT ID, COALESCE(First_name, Last_name), age FROM old_table;
Eggplant
  • 1,903
  • 1
  • 14
  • 24
1

Query:
INSERT INTO new_table (ID, name, age) SELECT ID, COALESCE(First_name,Last_name), age FROM old_table;

COALESCE will go through the listed values and choose the first one that isn't NULL It's more portable code than ISNULL or IVNL, etc.

Mr. Mascaro
  • 2,693
  • 1
  • 11
  • 18