Problem with "load data local infile"

[joeywoodbury]

OK I give up on searching the forums. No one is trying to use the replace operation.

I am trying to load a db into a table using the load data local infile. It loads it just fine, but what I am wanting is it to replace the data and not add to it. Right now it is is duplicating the data. Any suggestions? The code is below:

$query=mysql_query("load data local infile \"data.txt\" replace into table members fields enclosed by '\"' terminated by ','")or die("Error Executing the Query");

[khaine]

Does the data to be replaced have a unique record key if not the data will just be appended.

From the mysql documentation.

The REPLACE and IGNORE keywords control handling of input records that duplicate existing records on unique key values. If you specify REPLACE, new rows replace existing rows that have the same unique key value. If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you don't specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored

Does the data completely replace the data in the database? if it does why not just flush the database first.

Mark.

[joeywoodbury]

Thanks for your suggestion, I was kind of thinking of deleting the data first.

So I am going with the flushing the database. I am just using the simple DELETE FROM tbl_name, does anyone see any problems doing this? Is there a more optimized way of doing this?

[khaine]

Best bet is to use the flush command.

http://www.mysql.com/doc/F/L/FLUSH.html

Mark.