Neither LOAD DATA LOCAL..REPLACE or INSERT..ON DUPLICATE are working. Something wrong with my table structure?

I'm loading files into a table with php. I create a table like this:

CREATE TABLE IF NOT EXISTS $table (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`order` INT DEFAULT 0,
`data` VARCHAR(200) UNIQUE KEY,
`cur_timestamp` TIMESTAMP DEFAULT NOW())"

And fill it from a text file like this:

LOAD DATA LOCAL 
INFILE '".$file ."' 
REPLACE INTO TABLE $table 
FIELDS TERMINATED BY '^' 
(`order`,`data`)"

I've also tried reading the file into an array and just using an INSERT .. ON DUPLICATE KEY

INSERT INTO $table (`order`,`data`)
VALUES ($parts[0],'$parts[1]') 
ON DUPLICATE KEY UPDATE `order` = '$parts[0]'

In both cases if data is the the same in the new a开发者_运维问答nd old record but order is different it INSERTs. I expect it to UPDATE if data is the same. I expect my tables not set up properly, but can't see what the problem is. Can anyone tell me why it doesn't UPDATE when data matches?

1. You have to insert data including primary key- here id. In that case if old and new id are same, then data will be replaced- otherwise not. But your PK is AUTO_INC, so alternatively you can use an unique index column like order or anything else. For reference, please see - Replace

2. To print an array content, you need to use {} like

   "INSERT INTO $table (order,data) VALUES ({$parts[0]},'{$parts1}') ON DUPLICATE KEY UPDATE order = '{$parts[0]}'"

   or concatenation

Hope this may help you

Sadat