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

Question

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 and 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?

Answer 1

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