Combining two tables and updating cells where IDs match

Question

I have two tables with customer data which I wish to combine. One with very old data between 2013-2017 and one with data from 2018-now.

I basically want to stick the old data under the new data using UNION which is simple enough. However, I have old customers who exist in both tables (can be joined on customer_id).

Ideally, the query I write will do the following:

If the customer_id is present in the new data, it will just update newtable.starting_date with oldtable.starting_date.

If the customer_id is NOT present in the new data, the entire row will be added to the table.

I am doing this in MySQL.

`INSERT .. ON DUPLICATE KEY UPDATE` – Akina Nov 12 '20 at 17:01 — Akina, Nov 12 '20 at 17:01
See the manual under INSERT – Strawberry Nov 12 '20 at 17:02 — Strawberry, Nov 12 '20 at 17:02

score 0 · Answer 1 · answered Nov 12 '20 at 17:02

Do you want insert ... on duplicate key?

For this to work, you need a unique or primary key on column customer_id in the target table.

Assuming that both tables have three columns (customer_id, starting_date, ending_date), you can then phrase the query as:

insert into newtable (customer_id, starting_date, ending_date)
select customer_id, starting_date, ending_date
from oldtable
on duplicate key update starting_date = values(starting_date)

score -1 · Answer 2 · answered Nov 12 '20 at 17:02

MySQL does not support full outer join. But you can do:

select coalesce(o.starting_date, n.starting_date) as starting_date,
       . . .
from newdata n left join
     olddata o
     on n.customer_id = o.customer_id
union all
select . . .
from olddata o
where not exists (select 1 from newdata n where n.customer_id = o.customer_id);

Combining two tables and updating cells where IDs match

2 Answers2