SQL Error (1241): Operand should contain 1 column(s)
Posted by: Abhishek Gupta
Date: February 14, 2021 12:38PM

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')
;

Options: ReplyQuote


Subject
Written By
Posted
SQL Error (1241): Operand should contain 1 column(s)
February 14, 2021 12:38PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.