LOAD DATA INFILE REPLACE with specific columns

Question

I am currently using LOAD DATA LOCAL INFILE successfully, and typically with REPLACE. However, I am now trying to use the same command to load a CSV file into a table but only replace specific columns.

For example, table currently looks like:

ID    date        num1    num2
01    1/1/2017    100     200
01    1/2/2017    101     201
01    1/3/2017    102     202

where ID and date are the primary keys.

I have a similar CSV, but one that only has ID, date, num1 as columns. I want to load that new CSV into the table, but maintain whatever is in num2.

My current code per this post:

LOAD DATA LOCAL INFILE mycsv.csv
REPLACE INTO TABLE mytable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID, date, num1)

Again, my code works flawlessly when I'm replacing all the columns, but when I try to replace only select columns, it fills the other columns with NULL values. The similar posts (like the one I referenced) haven't helped.

I don't know if this is python specific issue, but I'm using MySQLdb to connect to the database and I'm familiar with the local_infile parameter and that's all working well. MySQL version 5.6.33.

Did you solve it? If yes, how? – Saurabh Goyal Jun 05 '18 at 13:22 — Saurabh Goyal, Jun 05 '18 at 13:22

score 0 · Answer 1 · answered Dec 07 '17 at 18:55

0

Simply load csv table into a similarly structured temp table and then run UPDATE JOIN:

CREATE TABLE mytemptable AS SELECT * FROM mytable LIMIT 1;          --- RUN ONLY ONCE

DELETE FROM mytemptable;

LOAD DATA LOCAL INFILE mycsv.csv
INTO TABLE mytemptable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID, date, num1);

UPDATE mytable t1
INNER JOIN mytemptable t2 ON t1.ID = t2.ID AND t1.`date` = t2.`date`
SET t1.num1 = t2.num1;

answered Dec 07 '17 at 18:55

Parfait

104,375
17
94
125

do we have any other options to achieve the same without using a temp table? Is there is any way we can provide specific fields to be replaced in the load data query itself? – Parth Vora Oct 16 '19 at 07:49
Unfortunately, not. `LOAD DATA` *appends* data and does not *update* data and additionally CSV is not a table that can be queried. Remember the S in SQL: structured. Or in other words, SQL is run on structured data. – Parfait Oct 16 '19 at 14:30

LOAD DATA INFILE REPLACE with specific columns

1 Answers1