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

Question

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

Answer 1

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.

• Busman: I had to add the keyword "table" after "into", and I had to remove the columns in the parentheses to get the query to work without syntax errors: LOAD DATA INFILE 'my path' INTO table tempTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'