3

I have a table looks like this. 

create table test1 (
id1 varchar(10),
id2 varchar(10),
value1 varchar(10),
value2 varchar(10),
primary key (id1, id2)
);

which contains the data looks like this:

id1    id2    value1    value2
stan   marsh    1         2
eric   cartman  3         4

I have a local CSV file which contains the updated data (contains new and old) looks like this:

"stan", "marsh", "5", "6"
"kyle", "broflovski", "7", "8"

How to import the csv file an meanwhile, update on duplicate key.

Few Links that might be helpful

  1. Import and overwrite existing data in MySQL (Same question but it was not answered properly)
  2. Mysql Insert on duplicate
Community
  • 1
  • 1
B.Mr.W.
  • 18,910
  • 35
  • 114
  • 178
  • I believe this is the link you need: http://dev.mysql.com/doc/refman/5.7/en/load-data.html Look at the `REPLACE` clause. – PM 77-1 Sep 16 '13 at 18:02
  • 2
    Possible duplicate, and if not it seems to answer the question: http://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update – Tricky12 Sep 16 '13 at 18:04
  • Hello friends, I have a table with 7 columns, the primary key does not work for me very much for this query, since it is based on columns and values, I want to insert or update a single column from a csv file; The problem is that Duplicate is not used correctly, and if possible for this scenario: if in a row three of the columns A, B, C match their values (already exists a record) do the update; If there is no match make an insert in the queue. –  Nov 09 '16 at 21:53

1 Answers1

3

Thanks a lot for the tiny flag advised by PM 77-1. 

load data local infile '/Users/myuserID/Desktop/test1.csv' 
replace 
into table test.test1 
columns terminated by ',' 
enclosed by '"' 
lines terminated by '\n';
B.Mr.W.
  • 18,910
  • 35
  • 114
  • 178
  • 2
    I see this is an old answer, but be careful with `REPLACE`, it will perform a `DELETE` then `INSERT`. If you have foreign keys, you may loose the child data. See http://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update – Ben Keene Jun 15 '15 at 14:00
  • Hello friends, I have a table with 7 columns, the primary key does not work for me very much for this query, since it is based on columns and values, I want to insert or update a single column from a csv file; The problem is that Duplicate is not used correctly, and if possible for this scenario: if in a row three of the columns A, B, C match their values (already exists a record) do the update; If there is no match make an insert in the queue. –  Nov 09 '16 at 21:53