Update MySQL table records using CSV

user_8023708 0
Viewed 938

I'm using MySQL with 5.7.21-21 version, and I have a table shipping_rate with structure like this:

+---------+----+
|entity_id|rate|
+---------+----+

i want to update the record using csv file using MySQL command line, here's what my csv file look like:

enter image description here

i tried to follow this solution, and modify some code so it will fit my table:

CREATE TEMPORARY TABLE temp_update_table (entity_id,rate)

LOAD DATA INFILE 'sr.csv' 
INTO TABLE temp_update_table FIELDS TERMINATED BY ',' (entity_id, rate); 

UPDATE shipping_rate
INNER JOIN temp_update_table on temp_update_table.entity_id = shipping_rate.entity_id
SET shipping_rate.rate = temp_update_table.rate;

DROP TEMPORARY TABLE temp_update_table;

but i always got an error like this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' rate)

import-from-csv csv mysql
1 Answers

You're missing the datatypes for the columns in the temp_update_table table.

CREATE TABLE temp_update_table (
    entity_id INT,
    rate INT,
    INDEX (entity_id)
);

It's also a good idea to add an index on the column used in the join.

Since your CSV file has a header line that should be skipped, you need to use the IGNORE clause.

LOAD DATA INFILE 'sr.csv' 
INTO TABLE temp_update_table 
FIELDS TERMINATED BY ',' 
IGNORE 1 LINES
(entity_id, rate);
user_14918950
Related