We help IT Professionals succeed at work.

Problem with ON DUPLICATE KEY UPDATE

Eddie Shipman
Eddie Shipman asked
on
3 Comments
1 solution
Medium Priority
290 Views
Last Modified: 2012-05-12
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?
Comment
Watch Question

View Solution Only

mankowitz
CERTIFIED EXPERT

Commented:
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())
Senior Developer
CERTIFIED EXPERT
Commented:
You have a missing UNIQUE KEY declaration on your CREATE TABLE structure that is why it is not working. Supposedly, there should be a UNIQUE KEY `u1` (`incremental_id`, `order_id`).

The whole CREATE TABLE:

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,
        UNIQUE KEY `u1` (`incremental_id`, `order_id`),
      INDEX `Index 1` (`attempt_id`, `incremental_id`, `order_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.