3
table name: mysample.
table columns: id - PK, names - pk, age.

inside mysample:

id    names      age
1    Nicole       19  
2    Sam          20  
3    Mark         15

I'm using MySQL Workbench.

That is the result after I use this query:

load data local infile 'C:/Users/mysample.csv' into table mysample fields terminated by ','
enclosed by '"'
lines terminated by '\n'
ignore 1 lines
(names, age)

This will load the csv file into the database. I modified the csv file and made these changes.

inside modifies csv file:

names       age
Nicole       20  
Sam          15  
Mark         13
May          10

What I want to achieve is that the rows Nicole, Sam and Mark will be updated thus having no similar names. That's why I made the names as the primary key. I searched and found the REPLACE query syntax.

load data local infile 'C:/Users/mysample.csv' replace into table mysample fields    
terminated by ','
enclosed by '"'
lines terminated by '\n'
ignore 1 lines
(names, age)

But I got these results on my database:

id    names        age
1     Nicole       19
2     Sam          20
3     Mark         15
4     Nicole       20
5     Sam          15
6     Mark         13
7     May          10

How can I specify the REPLACE query to replace the rows with the same primary key?

Sunny Sharma
  • 3,909
  • 5
  • 27
  • 64
nicole101
  • 167
  • 3
  • 18
  • 1
    Just make ID is the only primary key. remove name from the primary key – Madhawas Mar 27 '14 at 04:55
  • [**make names UNIQUE instead of primary this way it will not register repeated names again.**](http://stackoverflow.com/a/13425727/342740) – Prix Mar 27 '14 at 05:26
  • that is just an example. what my real data has a sales order item number and that will be my primary key. I removed the column with an auto_increment and made the names my only primary key and it worked when I tried it on this sample table and datas. But when I used it on my actual data, I get the error Error Code: 1366. Incorrect integer value: '' for column 'coupon_percent' at row 1. Now I don't know what went wrong. – nicole101 Mar 27 '14 at 07:49
  • I don't have any auto_increment anymore on my actual data. instead i have decimal values. what I used is decimal(6,2). – nicole101 Mar 27 '14 at 07:51

1 Answers1

1

You can try load your new data to a temporary table. Then use the following query to update your table:

update mysample a set 
age = (select t.age from temp_table t where t.names = a.names);

After that if you need to insert the new rows with the new values of names, you may execute:

insert into mysample
select * from temp_table t where t.names not in (select names from mysample);
curandero
  • 141
  • 3