0

mySQL – Updating an existing table with the contents of an imported CSV file

This is going to be a 2-part operation. First we create a temporary table to house the stuff we’re importing, then we run a query to reconcile the records between the temporary table and the table that we want to update.

“LINES TERMINATED BY ‘rn’” should end in ‘r’ if the source data is coming from a Linux system. ‘rn’ is necessary for importing data from a Windows system due to the extra characters Windows encodes in line breaks.

CREATE TEMPORARY TABLE imports LIKE existingtable;

LOAD DATA INFILE ‘C:\wamp\sourcedata.csv’
INTO TABLE imports
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘rn’
(id, quantity, …);

UPDATE existingtable
INNER JOIN imports on existingtable.id = imports.id
SET existingtable.quantity = imports.quantity;

DROP TEMPORARY TABLE imports;

Leave a Reply