Page 1 of 1

Replace from a file Syntax

Posted: Fri May 14, 2004 5:37 pm
by Sniper007
I'm trying to use the replace function to do exactly what is described in the manual. The twist is that I want to replace the rows with data that is in a text file. The manual seems only to indicate how to replace one row at a time:

Code: Select all

REPLACE їLOW_PRIORITY | DELAYED]
    їINTO] tbl_name ї(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
I need to replace 12,000 lines. I tried using the following syntax:

Code: Select all

LOAD DATA INFILE 'E:\AHLog.txt' REPLACE INTO TABLE `items` 
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\r\n'
But that didn't replace anything. It just created duplicate rows. I DO have unique indexes defined (three of them actually) for the table in question.

Is there some other way I can insert the values found in my text file into the first REPLACE expression? Somewhere after VALUES maybe?

Posted: Sat May 15, 2004 5:48 am
by launchcode
Replace fails when it cannot find a unique record to overwrite based on the new data you're trying to insert. The most common cause being a lack of indexes (which isn't your problem), or indexes that don't allow for the replace to identify unique records, usually caused by having say 3 separate indexes when you really need 1 joined one also.

I found that datatypes like a timestamp/datetime will cause a replace to fail unless I create an index bound on that field with something else unique (auto inc ID perhaps).

Slowly but surely

Posted: Sun May 16, 2004 12:08 am
by Sniper007
Alright, here's what I did:

I changed the two of the three column datatypes from TEXT to VARCHAR (the third column is datatype INT) so that I could create one index that had all three column fields in it. For some reason, I was not able to create a multiple column index when the fields were of datatype TEXT. Then I took that index that I created, and changed it's type to UNIQUE. That worked. Now when I try to insert data from a text file using the previously posted code, it says that twice as many rows were edited as I tried to insert (I assume it's doing some sort of insert-then-delete thingy) and when I browse the table, it's got NO DUPLICATES! Horray!!!

Thanks launchcode!!!

Please see my other post in regards to my schizophrenic phpMyAdmin:
viewtopic.php?p=107865#107865

:wink: