MySQL Workbench: I need to update values in a table based on values from a csv

csvimportMySQLreplace

Newbie Problem, but I've tried creating a new table and importing the csv to that then updating, but I have been having trouble with that, any guidance would be greatly appreciated.

I have a csv file that contains about 1500 entries, with an "ID" column and an "Unit" column. I am trying to figure out the best way to update, in my mySQL workbench table, the "unit" of only the IDs in my csv file.

CSV File

id_inv  unit
1,      aaa
2,      aaa
3,      aaa
5,      aaa
10,     aaa

DB Table

id_inv       unit     ... other columns
1            bbb
2            bbb
3            bbb
4            bbb
5            bbb
6            bbb
7            bbb
8            bbb
9            bbb
10           bbb

Best Answer

Import CSV file to temporary table and then run the UPDATE. For example:

DROP TEMPORARY TABLE IF EXISTS tempTable; 
CREATE TEMPORARY TABLE tempTable
(id_inv int,
unit varchar(255),
PRIMARY KEY (id_inv)
);

LOAD DATA INFILE 'C:\\PathToYour\\FileName.csv'
INTO tempTable (id_inv, unit)
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

UPDATE DBtable t1
JOIN tempTable t2 ON t1.id_inv = t2.id_inv
SET t1.unit = t2.unit;

DROP TEMPORARY TABLE tempTable;

You may need to use different symbols for fields and lines terminators and file path of course. Also if you are working with remote server use "LOAD DATA LOCAL INFILE" command.

Related Question