1

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

1 Answer 1

4

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.

1
  • 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';
    – Busman
    Aug 19, 2021 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.