<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=299788&amp;fmt=gif">

Bulk Data Update Made (Slightly) Easier

Software Development, Software Solutions

By Ryan Peterson

Something that comes along every once in awhile is having to import/update client records in MySQL. For example, the client submits a spreadsheet with "updated" values that need to be merged into the database, in this case emails:

FIRST_NAME LAST_NAME EMAIL COMPANY
Tom Jones tjones@totallyfake.com FakeCompany
Phil Collins philc@totallyfake.com FakeCompany
Jane Doe jd@totallyfake.com FakeCompany
How to merge these values into the existing table when there are hundreds of values, though? The following is one approach I've taken that seems fairly efficient:
First I save the list as CSV, single-quoting the string entries, and adding left/right parentheses:
('Tom','Jones','rjones@fake.com','FakeCompany'),
('Phil','Collins','philC@fake.com','FakeCompany'),
('Jane','Doe','jd@blah.com','FakeCompany')
Next I create a temp table based on the existing table structure:
CREATE TEMPORARY TABLE temp_update_email AS (SELECT first_name, last_name, email, company FROM contact);
Then I cleared out the temp table (so only the new records will populate it):
TRUNCATE TABLE temp_update_email;
Now to fill the temp table with the new data:
INSERT INTO temp_update_email(first_name, last_name, email, company)
VALUES ('Tom','Jones','tjones@fake.com','FakeCompany'),
('Phil','Collins','philC@fake.com','FakeCompany'),
('Jane','Doe','jd@blah.com','FakeCompany');
Lastly, update the existing table with the updated data:
UPDATE contact as c
INNER JOIN temp_update_email as t
ON c.first_name = t.first_name AND c.last_name = t.last_name AND c.company = t.company
SET c.email = t.email
WHERE c.company = 'FakeCompany';
It's not rocket science, but takes a little bit of manipulation to get going. Hopefully this will save someone a few minutes when trying to import a spreadsheet worth of values to their database.

TAGS: Software Development, Software Solutions

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Subscribe to Our Newsletter

Recent Blog Posts