asked on

Problem with ON DUPLICATE KEY UPDATE

I have a table defined like this:

CREATE TABLE `sync_orders_attempts` (
	`attempt_id` INT(11) NOT NULL AUTO_INCREMENT,
	`incremental_id` INT(11) NULL DEFAULT NULL,
	`order_id` INT(11) NULL DEFAULT NULL,
	`attempt_time` DATETIME NULL DEFAULT NULL,
	`raw_response` TEXT NULL,
	`update_response` TEXT NULL,
	`soap_data` VARCHAR(5000) NULL DEFAULT NULL,
	`update_data` TEXT NULL,
	INDEX `Index 1` (`attempt_id`, `incremental_id`, `order_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

Open in new window

When running an insert on duplicate key update query, it is ignoring the duplicate key.

INSERT INTO SYNC_ORDERS_ATTEMPTS (`incremental_id`, 
                                  `order_id`, 
                                  `raw_response`, 
                                  `update_response`, 
                                  `soap_data`, 
                                  `update_data`,  
                                  `attempt_time`) 
                          VALUES (0,              
                                  0,
                                  'XXX',
                                  'XXXXXX',
                                  'XXXXXXX',
                                  'XXXXXXXXXX',
                                  now())
ON DUPLICATE KEY UPDATE `incremental_id`  = VALUES(`incremental_id`), 
                        `order_id`        = VALUES(`order_id`), 
                        `raw_response`    = VALUES(`raw_response`), 
			`update_response` = VALUES(`update_response`), 
                        `soap_data`       = VALUES(`soap_data`), 
			`update_data`     = VALUES(`update_data`), 
			`attempt_time`    = VALUES(`attempt_time`)

Open in new window

If I remove the autoinc from the attempt_id column and the index, it still ignores the duplicate key.
How do I fix this?

mankowitz

if you're not doing any manipulation of the data, have you considered using

INSERT REPLACE SYNC_ORDERS_ATTEMPTS (`incremental_id`,
`order_id`,
`raw_response`,
`update_response`,
`soap_data`,
`update_data`,
`attempt_time`)
VALUES (0,
0,
'XXX',
'XXXXXX',
'XXXXXXX',
'XXXXXXXXXX',
now())

ASKER CERTIFIED SOLUTION

Armand G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nem Schlecht

Just want to say armchang is 100% correct. Having just an INDEX will *not* cause a duplicate key collision - you *must* have a UNIQUE index of some sort.