cannot replace contents of atable using load data local infile...

Question

hi every one.. load data local infile 'input.txt' replace into table t fields terminated by '|'; doesn't seem to replace the former contents, its creating duplicate copies of the entries, i tried with 'ignore' also,but even it is not working..pls tel me where i'm going wrong..

i created d table using...

create table t 
(name varchar(20),empid int,age int,dob date,`order` int,price int);

my query..

load data local infile 'empinput.txt' 
replace into table t fields terminated by '|' ignore 5 lines;

or

load data local infile 'empinput.txt' 
ignore into table t fields terminated by '|' ignore 5 lines;

doesn't seem to work.. my input.txt file has..

 gopakumar  |     3 |  20 | 1990-12-03 |     1 |    24 
 nitin      |     4 |  18 | 1992-07-30 |     2 |    48 
 prashant   |     5 |  20 | 1990-05-17 |     3 |    72 
 arunakumar |     2 |  21 | 1989-12-08 |     4 |    96 
 ravikiran  |    10 |  26 | 1984-06-14 |     6 |   144 
 abhi       |    32 |  21 | 1989-03-21 |     7 |   168 
 Amruthesh  |     1 |  22 | 1988-09-22 |     8 |   192 
 abcd       |    56 |  21 | 1989-09-09 |  NULL |   200 
 abhi       |    23 |  20 | 1990-08-08 |    12 |  1060 

thanks in advance....

Answer 1

REPLACE will only replace rows with the same primary key (or unique index). For example if you move a table out of mysql, modify it and load again, it will update the original rows and add the new ones. If you want to replace the rows when possible, you need to have the same Primary Key value of the replaced row in your imported file.

MysqL Load Data Infile Reference