Description:
BINLOG_FORMAT = STATEMENT
target table has an autoincrement column, but it isn't the one being updated
another table is being joined, but there is no order by
I also tried creating an temp table and joining on the PK, but that made no difference for this case
How to repeat:
table definitions and a sample query:
CREATE TABLE `tbl_xrays_done_per_visit` (
`Visit_Code` int(11) DEFAULT NULL,
`Xray_Type_ID` int(11) DEFAULT NULL,
`Side_ID` int(11) DEFAULT NULL,
`Xray_UID` int(11) NOT NULL AUTO_INCREMENT,
`TimeDone` datetime DEFAULT NULL,
`Tech_ID` int(11) DEFAULT '0',
`Xray_Diagnosis` text,
`signedBy_ID` int(11) DEFAULT NULL,
`noReportNeeded` tinyint(1) DEFAULT '0',
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Xray_UID`),
KEY `Side_ID` (`Side_ID`),
KEY `Tech_ID` (`Tech_ID`),
KEY `TimeDone` (`TimeDone`),
KEY `Visit_Code` (`Visit_Code`),
KEY `Xray_Type_ID` (`Xray_Type_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=101236 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_visits` (
`Visit_Code` int(11) NOT NULL AUTO_INCREMENT,
`Visit_Date` datetime DEFAULT NULL,
`Last_Name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`First_Name` varchar(20) DEFAULT NULL,
`last_name_e` varchar(20) DEFAULT NULL,
`first_name_e` varchar(20) DEFAULT NULL,
`ID_Num` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`DOB` date DEFAULT NULL,
`Gender` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`PhoneNum_Home` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`PhoneNum_Mobile` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`Complaint` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`BranchCode` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`referring_Doctor` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Visit_Code`),
KEY `Visit_Date` (`Visit_Date`),
KEY `BranchCode` (`BranchCode`)
) ENGINE=InnoDB AUTO_INCREMENT=95015 DEFAULT CHARSET=utf8;
CREATE TEMPORARY TABLE IF NOT EXISTS t_visits AS (select x.Xray_UID from tbl_xrays_done_per_visit x
inner join tbl_visits v on v.visit_code=x.visit_code
where
TimeDone>=adddate(now(),INTERVAL -7 DAY)
and x.noReportNeeded=1
and v.Complaint in('something')
limit 10);
update tbl_xrays_done_per_visit x
inner join t_visits v on v.Xray_UID=x.Xray_UID
set x.noReportNeeded=1 ;
Description: BINLOG_FORMAT = STATEMENT target table has an autoincrement column, but it isn't the one being updated another table is being joined, but there is no order by I also tried creating an temp table and joining on the PK, but that made no difference for this case How to repeat: table definitions and a sample query: CREATE TABLE `tbl_xrays_done_per_visit` ( `Visit_Code` int(11) DEFAULT NULL, `Xray_Type_ID` int(11) DEFAULT NULL, `Side_ID` int(11) DEFAULT NULL, `Xray_UID` int(11) NOT NULL AUTO_INCREMENT, `TimeDone` datetime DEFAULT NULL, `Tech_ID` int(11) DEFAULT '0', `Xray_Diagnosis` text, `signedBy_ID` int(11) DEFAULT NULL, `noReportNeeded` tinyint(1) DEFAULT '0', `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`Xray_UID`), KEY `Side_ID` (`Side_ID`), KEY `Tech_ID` (`Tech_ID`), KEY `TimeDone` (`TimeDone`), KEY `Visit_Code` (`Visit_Code`), KEY `Xray_Type_ID` (`Xray_Type_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=101236 DEFAULT CHARSET=utf8; CREATE TABLE `tbl_visits` ( `Visit_Code` int(11) NOT NULL AUTO_INCREMENT, `Visit_Date` datetime DEFAULT NULL, `Last_Name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `First_Name` varchar(20) DEFAULT NULL, `last_name_e` varchar(20) DEFAULT NULL, `first_name_e` varchar(20) DEFAULT NULL, `ID_Num` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `DOB` date DEFAULT NULL, `Gender` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `PhoneNum_Home` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `PhoneNum_Mobile` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `Complaint` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `BranchCode` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `referring_Doctor` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`Visit_Code`), KEY `Visit_Date` (`Visit_Date`), KEY `BranchCode` (`BranchCode`) ) ENGINE=InnoDB AUTO_INCREMENT=95015 DEFAULT CHARSET=utf8; CREATE TEMPORARY TABLE IF NOT EXISTS t_visits AS (select x.Xray_UID from tbl_xrays_done_per_visit x inner join tbl_visits v on v.visit_code=x.visit_code where TimeDone>=adddate(now(),INTERVAL -7 DAY) and x.noReportNeeded=1 and v.Complaint in('something') limit 10); update tbl_xrays_done_per_visit x inner join t_visits v on v.Xray_UID=x.Xray_UID set x.noReportNeeded=1 ;