I have a problem updating table using View, as the program returns "0 rows updated when that should have been 1." error.
Strangely when I perform the same update using Navicat or phpMyAdmin, everything works well.
Table definitions:
DROP TABLE IF EXISTS `scq`;
CREATE TABLE `scq` (
`SCQID` int(11) NOT NULL AUTO_INCREMENT,
`NicolaID` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`IssueSCQ` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`SCQAccepted` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`SCQ_RefusalReason` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`SCQVersionold` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`scqVersion` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`SCQ_checkcode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`SCQID`),
KEY `SCQ_checkcode` (`SCQ_checkcode`) USING BTREE,
KEY `NicolaID` (`NicolaID`) USING BTREE,
CONSTRAINT `scq_ibfk_1` FOREIGN KEY (`NicolaID`) REFERENCES `ids` (`NicolaID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=507 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `ids`;
CREATE TABLE `ids` (
`NicolaID` varchar(10) CHARACTER SET utf8 NOT NULL,
`ParticipantID` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Respondent_Serial` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`HHNO` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`HHNOX` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`NicolaID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `personal`;
CREATE TABLE `personal` (
`PersonalID` int(11) NOT NULL AUTO_INCREMENT,
`NicolaID` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Title` varchar(6) CHARACTER SET utf8 DEFAULT NULL,
`FirstName` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`LastName` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`InterviewDate` varchar(10) DEFAULT NULL,
`TelephoneNo` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`EmailAddress` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
`ConsentToContact` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`Gender` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`ReportedAge` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`MonthOfBirth` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`YearOfBirth` varchar(5) CHARACTER SET utf8 DEFAULT NULL,
`IncentivePayeeName` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`PersonalID`),
KEY `NicolaID` (`NicolaID`) USING BTREE,
CONSTRAINT `personal_ibfk_1` FOREIGN KEY (`NicolaID`) REFERENCES `ids` (`NicolaID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=508 DEFAULT CHARSET=latin1;
View code that is causing problem:
select `scq`.`SCQID` AS `SCQID`,`scq`.`NicolaID` AS `NicolaID`,`ids`.`ParticipantID` AS `ParticipantID`,`personal`.`InterviewDate` AS `InterviewDate`,`scq`.`IssueSCQ` AS `IssueSCQ`,`scq`.`SCQAccepted` AS `SCQAccepted`,`scq`.`SCQ_RefusalReason` AS `SCQ_RefusalReason`,`scq`.`SCQVersionold` AS `SCQVersionold`,`scq`.`scqVersion` AS `scqVersion`,`scq`.`SCQ_checkcode` AS `SCQ_checkcode`
from ((`scq` join `ids` on((`scq`.`NicolaID` = `ids`.`NicolaID`))) join `personal` on((`ids`.`NicolaID` = `personal`.`NicolaID`)));
then when I attemp to change SCQ_checkcode by typing into data window, the results are:
UPDATE `CAPI`.`scqView4Angie` SET `SCQ_checkcode`='23cd' WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `InterviewDate`='2014-03-27' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='' LIMIT 1;
SHOW WARNINGS;
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0.000 sec. */
I need to add, that I have modified this view by adding InterviewDate from table personal to it. Previous version of this view did not have any problems with updating/inserting data:
select `scq`.`SCQID` AS `SCQID`,`scq`.`NicolaID` AS `NicolaID`,`ids`.`ParticipantID` AS `ParticipantID`,`scq`.`IssueSCQ` AS `IssueSCQ`,`scq`.`SCQAccepted` AS `SCQAccepted`,`scq`.`SCQ_RefusalReason` AS `SCQ_RefusalReason`,`scq`.`SCQVersionold` AS `SCQVersionold`,`scq`.`scqVersion` AS `scqVersion`,`scq`.`SCQ_checkcode` AS `SCQ_checkcode`
from (`scq` join `ids` on((`scq`.`NicolaID` = `ids`.`NicolaID`)));
query produces results:
UPDATE `CAPI`.`scqView4Angie-OLD` SET `SCQ_checkcode`='23cd' WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='' LIMIT 1;
SELECT `SCQID`, `NicolaID`, `ParticipantID`, `IssueSCQ`, `SCQAccepted`, `SCQ_RefusalReason`, `SCQVersionold`, `scqVersion`, `SCQ_checkcode` FROM `CAPI`.`scqView4Angie-OLD` WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='23cd' LIMIT 1;
UPDATE `CAPI`.`scqView4Angie-OLD` SET `SCQ_checkcode`='23cd' WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='' LIMIT 1;
SELECT `SCQID`, `NicolaID`, `ParticipantID`, `IssueSCQ`, `SCQAccepted`, `SCQ_RefusalReason`, `SCQVersionold`, `scqVersion`, `SCQ_checkcode` FROM `CAPI`.`scqView4Angie-OLD` WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='23cd' LIMIT 1;
SHOW WARNINGS;
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0.000 sec. */
There are no triggers nor procedures. I am just wondering why Heidi does not like my code while other programs do not complain.
I would appreciate any suggestions what is wrong or how to fix what is wrong.
Thanks
Maciej