Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ON DUPLICATE UPDATE not works with some row

Tags:

php

mysql

I am using ON DUPLICATE UPDATE on my query, some of the result didn't store it. I tried all the possible way, but those still remain the same. here are the database picture.

enter image description here

Those NULL, is the row which didn't store successfully; the result should be 1 instead of NULL.

    if($remark){
$query3 = "INSERT INTO `audit_section_remarkrecord` SET remark = '$remark', form_details_subquestion_id = '$form_details_subquestion_id', form_details_section_id = '$form_details_section_id', audit_section_no = '$audit_no' ON DUPLICATE KEY UPDATE
form_details_section_id = '$form_details_section_id' , remark = '$remark'";
$result3 = $db->query($query3); 

$query4 = "UPDATE `remarkrecord_update_details` SET form_details_section_id = '$form_details_section_id', userlog = '$user_staff', ipaddress = '$ip' WHERE form_details_subquestion_id = '$form_details_subquestion_id' AND audit_section_no = '$audit_no' ";
$result4 = $db->query($query4); 
}else{
}

}

Table Structure

enter image description here

like image 583
Andrew Avatar asked Oct 31 '22 14:10

Andrew


1 Answers

Try changing one of your columns you are inserting value to primary key or add UNIQUE constraint to the column.

ALTER TABLE audit_section_remarkrecord ADD UNIQUE KEY (your column);

As there is currently only a unique key on the Primary Key, an auto-increment, you are going to have difficulty generating a unique key clash on an insert on duplicate key update (IODKU). Therefore, some other unique key needs to be created (even a composite), that will trigger the clash of a unique key and allow IODKU to work as expected.

like image 61
Abhijith Sasikumar Avatar answered Nov 15 '22 07:11

Abhijith Sasikumar