I have a MYSQL table with demographic data. The addresses contained in the table were entered manually before the advent of online postcode lookup software and, hence, there are a lot of errors when using automated online mailing checking software. I plan to export the data as csv file for a 3rd party to do a bulk correction and then reimport the data. The problem is that the database will continue to be used and the demographic table also includes other data (such as dates and times, etc) than might change in the meantime, for example each row will have the following fields:
Obviously the address fields for some records will change in the csv file and the date fields may change in the original database table. So how do I go about importing the corrected data fields while leaving the unchanged fields that same? Would it be sensible to export only the address fields?
The most important requirement here would be a primary key on your table - a non-NULL column or set of columns that uniquely identify each record in your table.
If you have a primary key, your task will be much easier. If not, then it will be fiendishly difficult.
Looking at your column list, I assume that "ID" is the primary key column i.e. it has a value in each row and that value is unique across the entire table.
So you could do the following:
First determine which columns will be changed by the 3rd party. Is it just address2, or a bunch of columns like (street, address2, town) etc.?
Export the ID and the set of columns that could be updated by the 3rd party. If they need the other columns like (surname, firstname) etc. for context, then you could export them as well, but mention that any changes to those columns will not be applied in your original table. Essentially, you need to draw the line on changes to which columns are acceptable and which ones are not. Of course, the ID column shall not be changed. The primary key is immutable.
Your 3rd party gets the CSV file, updates the address column(s) and sends it back to you. I am assuming that they will not add any new records with new IDs; they will only update the existing records.
You load the updated records in a separate table in your database.
Join the original table with the updated table on the ID column and update only the address column(s) in the original table picking their new values from the updated table.
I do not have MySQL to work with at the moment, but I have created a few SQL statements and tested them in SQLFiddle. (www.sqlfiddle.com)
-- 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;