SQL Error (1241): Operand should contain 1 column(s)
Let me tell you my requirement.
Requirement: To update FTRIdentifier as NULL when you found same NMId and FTRIdentifier values in different row and keep the value untouch for the minimum value of auto-increment primary key FTRId.
============================================================================================================================================================
So, I created a multi table update query as below:
update T_FTR tf
inner join
(
select min(itf.FTRId) as minftrid, itf.NMId, itf.FTRIdentifier
from T_FTR itf
where itf.NMId IS NOT NULL and itf.FTRIdentifier IS NOT NULL
group by itf.NMId, itf.FTRIdentifier
having count(1) > 1
) fdt on fdt.NMId = tf.NMId and fdt.FTRIdentifier = tf.FTRIdentifier
SET tf.FTRIdentifier = NULL
where tf.NMId = fdt.NMId and tf.FTRIdentifier = fdt.FTRIdentifier and tf.FTRId > fdt.minftrid
;
Now it is giving SQL Error (1241): Operand should contain 1 column(s) in MySQL 5.7.16 but execute successfully on MySQL 5.5.51.
Under MySQL 5.5.51
===================
update T_FTR tf
inner join
(
select min(itf.FTRId) as minftrid, itf.NMId, itf.FTRIdentifier
from T_FTR itf
where itf.NMId IS NOT NULL and itf.FTRIdentifier IS NOT NULL
group by itf.NMId, itf.FTRIdentifier
having count(1) > 1
) fdt on fdt.NMId = tf.NMId and fdt.FTRIdentifier = tf.FTRIdentifier
SET tf.FTRIdentifier = NULL
where tf.NMId = fdt.NMId and tf.FTRIdentifier = fdt.FTRIdentifier and tf.FTRId > fdt.minftrid
;
/* Affected rows: 2 Found rows: 0 Warnings: 0 Duration for 1 query: 0.015 sec. */
============================================================================================================================================================
Table Schema
-------------
mysql> show create table t_ftr\G
*************************** 1. row ***************************
Table: t_ftr
Create Table: CREATE TABLE `t_ftr` (
`FTRId` int(11) NOT NULL AUTO_INCREMENT,
`FTRName` varchar(360) NOT NULL,
`Ver` varchar(80) DEFAULT NULL,
`Descr` varchar(510) DEFAULT NULL,
`NMId` int(11) NOT NULL,
`isEnabled` tinyint(1) NOT NULL DEFAULT '1',
`isDeployed` tinyint(1) NOT NULL DEFAULT '0',
`DefaultLMId` int(11) DEFAULT NULL,
`RefId1` varchar(100) DEFAULT NULL,
`RefId2` varchar(100) DEFAULT NULL,
`ExternalId` varchar(60) DEFAULT NULL,
`FileLoadId` int(11) DEFAULT NULL,
`FTRIdentifier` int(11) DEFAULT NULL,
`CREATEDBY` varchar(50) NOT NULL DEFAULT 'admin',
`CREATEDATE` datetime DEFAULT NULL,
`MODIFIEDBY` varchar(50) NOT NULL DEFAULT 'admin',
`MODIFIEDDATE` datetime DEFAULT NULL,
`globalid` varchar(50) NOT NULL,
PRIMARY KEY (`FTRId`),
UNIQUE KEY `uk_t_ftr_globalid` (`globalid`),
UNIQUE KEY `UK_FTR_FTRName_Ver` (`NMId`,`FTRName`(255),`Ver`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
============================================================================================================================================================
Sample Data
------------
INSERT INTO `t_ftr` (`FTRId`, `FTRName`, `Ver`, `Descr`, `NMId`, `isEnabled`, `isDeployed`, `DefaultLMId`, `RefId1`, `RefId2`, `ExternalId`, `FileLoadId`, `FTRIdentifier`, `CREATEDBY`, `CREATEDATE`, `MODIFIEDBY`, `MODIFIEDDATE`, `globalid`) VALUES
(1, 'FTRName1', '5.3', 'FeatureDescr1', 1, 1, 1, 1, NULL, NULL, NULL, NULL, 345, 'admin', '2018-10-17 13:23:38', 'admin', '2018-10-17 13:23:38', '95622c44-41ca-4456-97d1-497760f728f6'),
(2, 'FTRName2', '5.3', 'FeatureDescr2', 1, 1, 0, 15, '', '', '', NULL, 345, 'admin', '2018-10-17 13:23:39', 'admin', '2018-10-26 12:31:35', 'f0a3fbbc-09b8-47ec-b4c3-1ac4bbd11e3d'),
(3, 'FTRName3', '5.3', 'FeatureDescr3', 1, 1, 0, 15, '', '', '', NULL, 345, 'admin', '2018-10-17 13:23:39', 'admin', '2018-10-26 12:31:43', '158845c8-24e9-49e9-a190-dfde811a3209'),
(4, 'FTRName4', '5.3', 'FeatureDescr4', 1, 1, 1, 15, '', '', '', NULL, NULL, 'admin', '2018-10-17 13:23:39', 'admin', '2018-10-26 12:31:58', '004b72e7-e1fb-4161-a067-b4410ae888ad'),
(5, 'FTRName5', '5.3', 'FeatureDescr5', 1, 1, 1, 15, '', '', '', NULL, NULL, 'admin', '2018-10-17 13:23:40', 'admin', '2018-10-26 12:32:04', '6394fe8d-e767-43d3-ae9e-09b4337c2840')
;