CODEWITHSUNDEEP
mysqlduplicatesinsert-update
Zak
Zak

Reputation: 7523

MySQL ON DUPLICATE KEY UPDATE only inserting, not updating

I have dug through SO questions and none address my specific issue ... I have read the following relevant threads: Here, to no avail.

I have a simple table with the following data:

|-----------------------------------------------------------------------------|
|  id  |  contractor_id  |  section_id  |  page_id  | modified  |  timestamp  |    
|-----------------------------------------------------------------------------|

Here is the create statement:

CREATE TABLE `reusable_page_modified` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contractor_id` int(11) DEFAULT NULL,
  `section_id` int(11) DEFAULT NULL,
  `page_id` int(11) DEFAULT NULL,
  `modified` int(1) NOT NULL DEFAULT '1',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13844 DEFAULT CHARSET=utf8;

Now I have 4 rows in the db right now, and they are (I'll leave off id and timestamp since they are auto generated):

|---------------------------------------------------------|
|  contractor_id  |  section_id  |  page_id  |  modified  |
###########################################################
|       1016      |      309     |   10303   |     0      |
|----------------------------------------------------------
|       1017      |      309     |   10303   |     1      |
|----------------------------------------------------------
|       1073      |      309     |   10303   |     1      |
|----------------------------------------------------------
|        240      |      309     |   10303   |     1      |
|----------------------------------------------------------

I am focusing on the first line where modified is set to 0. What I want to do is set it to 1 if contractor_id, section_id and page_id all exist. If not, enter a new row.

This is what I have:

INSERT INTO `reusable_page_modified`
    (contractor_id, section_id, page_id, modified)
    VALUES ('1016', '309', '10303', '1')
    ON DUPLICATE KEY UPDATE 
    `section_id` = '309' 
    AND `page_id` = '10303' 
    AND `contractor_id` = '1016';

This creates a new row. I think I am not understanding the ON DUPLICATE KEY UPDATE statment the way it was intended. I have read the MySQL documentation Here and still no help. What am I not seeing here?

Upvotes: 0

Views: 4111

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

You are not far from being correct, but your syntax is a bit off. First, if you want ON DUPLICATE KEY to work when a record with duplicate values for the contractor_id, section_id, and page_id columns is inserted, you will need a unique constraint on these three columns. You can add one using the following:

ALTER TABLE reusable_page_modified
ADD CONSTRAINT u_cnst UNIQUE (contractor_id, section_id, page_id);

Next, for the actual INSERT statement you would use what you have is close, except that you update all the columns which don't require any updating. Instead, leave the three columns alone and instead update the modified column to 1:

INSERT INTO reusable_page_modified
    (contractor_id, section_id, page_id, modified)
VALUES ('1016', '309', '10303', '1')
ON DUPLICATE KEY UPDATE
    modified = 1

Upvotes: 3

Related Questions