How to solve duplicate entry for key 'PRIMARY' after load data infile

Question

So, basically I have this code below to change the date format from CSV, so it can be recognized by MySQL when I import it.

LOAD DATA INFILE 'file.csv'
INTO TABLE customer FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' 
(
    ID, name, @var1, age
)
SET date = STR_TO_DATE(@var1, '%d/%m/%Y')

Now, consider the ID is the PRIMARY key. I get an update on my file.csv and I want to re-load it to MySQL by running my code again. Instead only update the new record, it give me this error message:

#1062 - Duplicate entry '05' for key 'PRIMARY'

Is there any way to ignore the same ID and only add the new record with different ID?

you could use replace into or ignore in that cas. this link might help (https://dev.mysql.com/doc/refman/5.7/en/load-data.html) — Naveen Kumar Gupta, Jul 14 '17 at 03:46
https://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update — TheSatinKnight, Jul 14 '17 at 06:18

Hibari · Answer 1 · 2017-07-14T03:51:28.647

2

MySQL does not allow duplication of PKs because every Primary key is a Unique Key

What you can do, I suggest, is to AUTO_INCREMENT the ID. If you really want to insert the records rather than update it.

Also, make sure that when you insert the .csv file, it does not contain any ID. So, you'll want it to auto-populate along the table.

edited Jul 14 '17 at 03:51

answered Jul 14 '17 at 03:38

Hibari

131
16

And make sure you start AUTO_INCREMENT at the right ID value – Stephane Paquet Jul 14 '17 at 03:44

How to solve duplicate entry for key 'PRIMARY' after load data infile

1 Answers1