MySql Insert if not exist two column pair otherwis

问题:

I got Mysql table like this

CREATE  TABLE IF NOT EXISTS tbl_member_doc_read (
`read_id` INTEGER(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`member_id` INTEGER(10) UNSIGNED NOT NULL ,
`doc_id` INTEGER(10) UNSIGNED NOT NULL ,
`status` INTEGER(1) DEFAULT '0',
FOREIGN KEY (`member_id`) REFERENCES tbl_member(`member_id`),
FOREIGN KEY (`doc_id`) REFERENCES tbl_doc(`doc_id`)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

lets say example data id that table

read_id  member_id  doc_id  status
 1          1           1       1
 2          1           2       0
 3          2           2       1

now I want insert in that table if combine or pair (member_id,doc_id) is exists else update status if status is 0. here is sql query i used

INSERT INTO tbl_member_doc_read (member_id, doc_id, status) VALUES(1,2,1) ON DUPLICATE KEY UPDATE status = VALUES(status) WHERE status = 0;

it should update second row with data 2 1 2 1

and if i insert this

INSERT INTO tbl_member_doc_read (member_id, doc_id, status) VALUES(2,1,0) ON DUPLICATE KEY UPDATE status = VALUES(status) WHERE status = 0;

it should insert a new row with data 4 2 1 0

but I can't work this out. and also I think in innodb engine as member_id and doc_id is referenced to another table's primary key they are unique. and in innodb I can't create unique indexes.

Any help will be appreciated. thanks in advance.

回答1:

in order to work the ON DUPLICATE KEY UPDATE statement, you need to define unique key on the two columns,

CREATE  TABLE IF NOT EXISTS tbl_member_doc_read 
(
`read_id` INTEGER(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`member_id` INTEGER(10) UNSIGNED NOT NULL ,
`doc_id` INTEGER(10) UNSIGNED NOT NULL ,
`status` INTEGER(1) DEFAULT '0',
FOREIGN KEY (`member_id`) REFERENCES tbl_member(`member_id`),
FOREIGN KEY (`doc_id`) REFERENCES tbl_doc(`doc_id`),
CONSTRAINT tb_uq UNIQUE (member_id, doc_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

then remove the WHERE clause

INSERT INTO tbl_member_doc_read (member_id, doc_id, status) 
VALUES(1,2,1) 
ON DUPLICATE KEY UPDATE 
status = VALUES(status)

• SQLFiddle Demo