Ethan Allen Ethan Allen - 4 months ago 9
MySQL Question

How do I combine two rows into one single row by overriding NULL values using MySQL?

I have two rows like this:

id title format region discs user_id origin_id
------------------------------------------------------------------------
1 12 Monkeys DVD NULL NULL NULL NULL
2 Twelve Monkeys NULL NULL 1 2 1


I want to do a
SELECT
call that always returns the data when a
user_id
is not
NULL
. Here's what the result I am looking for should be:

id title format region discs user_id origin_id
-----------------------------------------------------------------------
2 Twelve Monkeys DVD NULL 1 2 1


As you can see,
format
got filled from
id 1
because of
NULL
on
id 2
, but everything else was filled from id 2.

What is the MySQL statement that can do this for me?

Answer

For your given data, you can use a left join and coalesce() to prioritize the values:

select coalesce(t.id, t2.id) as id,
       coalesce(t.title, t2.title) as title,
       coalesce(t.format, t2.format) as format,
       coalesce(t.region, t2.region) as region,
       coalesce(t.discs, t2.discs) as discs,
       t.user_id
from t left join
     t t2
     on t2.user_id is null
where t.user_id is not null;