Insert or update with select and override nulls

Question

I got two tables: defaults and users with same columns, except in defaults the column user_id is unique while in "users" it's not.

I want to take all the rows from users and insert them to defaults, if two rows in users got the same user_id, I want to merge them in such way that all the empty/null values will be overridden with non empty/null values

Here is an example

users
-----
user_id|name|email|address
--------------------------
1      |abc |null |J St
1      |coco|a@b.c|null

After inserting to defaults I expect the next result:

defaults
-----
user_id|name|email|address
--------------------------
1      |abc |a@b.c|J St

@Eric B provided an answer of how to do this with insert values:

Assuming 3 columns in the table.. ID, NAME, ROLE
This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

How to do this when I use select for my insert

insert or replace into defaults select ??? from users

Which one should be chosen if there is more than one non-NULL value? — CL., Jul 30 '16 at 06:55

score 1 · Accepted Answer · edited Jul 30 '16 at 08:08

1

INSERT OR REPLACE INT DEFAULTS VALUES (ID, Name, Role)
  (
    SELECT ID, MAX(Name), MAX(Role) FROM Users GROUP BY ID
  );

The max will select the max value instead of null if there is one.

edited Jul 30 '16 at 08:08

Golden Gate

1,132
4
14
25

answered Jul 30 '16 at 07:47

Osie J O'Connor

383
6
12

Insert or update with select and override nulls

1 Answers1