Replace from a file Syntax

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sniper007
Forum Newbie
Posts: 17
Joined: Tue May 11, 2004 8:43 pm

Replace from a file Syntax

Post 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?
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post 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).
Sniper007
Forum Newbie
Posts: 17
Joined: Tue May 11, 2004 8:43 pm

Slowly but surely

Post 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:
Post Reply