Code:
-- The original table
create table demographics1 (id int primary key, name varchar(10), dob date, city varchar(20), country varchar(2));
insert into demographics1 (id, name, dob, city, country) values
(1, 'Aaron', DATE'1980-01-01', 'London', 'UK'),
(2, 'Beth', DATE'1981-01-01', 'Chicago', 'US'),
(3, 'Colin', DATE'1982-01-01', 'Berlin', 'DE')
;
-- The new table loaded with the updated data in the CSV file obtained from the 3rd party.
create table demographics2 (id int primary key, name varchar(10), dob date, city varchar(20), country varchar(2));
insert into demographics2 (id, name, dob, city, country) values
(1, 'Aaron', DATE'1980-01-01', 'London', 'UK'),
(2, 'Beth', DATE'1981-01-01', 'New York', 'US'),
(3, 'Calvin', DATE'1982-11-11', 'Zurich', 'CH'),
(4, 'Dennis', DATE'1983-01-01', 'Tokyo', 'JP'),
(5, 'Emma', DATE'1984-01-01', 'Paris', 'FR')
;
--
select * from demographics1;
--
select * from demographics2;
-- Original data is in "lft" table alias, new data is in "rgt" table alias.
-- Only the updates to "city" and "country" will be applied to the original table.
-- Note that the change in "name" and "dob" for ID=3 will *NOT* be applied to the original table.
-- Also note that the new IDs (4, 5) in the "rgt" table will *NOT* be added to the original table.
update demographics1 as lft
inner join demographics2 as rgt on lft.id = rgt.id
set lft.city = rgt.city, lft.country = rgt.country;
--
select * from demographics1;
--
select * from demographics2;
