Update MySQL table records using CSV

Question

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:

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)

Barmar · Accepted Answer · 2019-01-17 03:11:01Z

3

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);

edited Jan 17, 2019 at 3:11

answered Jan 17, 2019 at 3:03

Barmar

789k57 gold badges554 silver badges668 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Hunter Over a year ago

it works, but when i select the first record of the temporary table it show enity_id and the rate is 0 result from select

Barmar Over a year ago

Your CSV file has a header line, so use the IGNORE 1 LINE clause in LOAD DATA INFILE to skip it.

Hunter Over a year ago

got this error

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 'IGNORE 1 LINE' at line 1

when i put the IGNORE 1 LINE at the end of LOAD DATA INFILE the mysql command look like this LOAD DATA INFILE 'sr.csv' INTO TABLE temp_update_table FIELDS TERMINATED BY ',' (entity_id, rate) IGNORE 1 LINE;

Barmar Over a year ago

It's LINES not LINE. I corrected my answer several minutes ago.

Barmar Over a year ago

It has to go before (entity_id, rate).

|

Collectives™ on Stack Overflow

Update MySQL table records using CSV

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related