Ilya_Gazman Ilya_Gazman - 4 months ago 8
SQL Question

Insert or update with select and override nulls

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

Answer
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.

Comments