Error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &l-CodePudding

I try to execute this query SQL

USE [transaction]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TRANSACT_1] 
AS
BEGIN
    SET NOCOUNT ON;

    WHILE ((SELECT [NO_ACCOUNT] FROM [transaction].DBO.ML_TRANS1) = '3C' AND (SELECT [TO_ACCOUNT] FROM [transaction].DBO.ML_TRANS1) = '1011')
        BEGIN 
            IF ((SELECT [SENDER] FROM [transaction].DBO.ML_TRANS1) <> 'ID')
            UPDATE [transaction].DBO.ML_TRANS1 SET [SENDER] = 'STS';
        END
END

And this is an error

This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

CodePudding user response：

Maybe this:

UPDATE ML_TRANS1
SET SENDER = 'STS'
WHERE NO_ACCOUNT = '3C' 
    AND TO_ACCOUNT = '1011'
    AND SENDER <> 'ID';

Nine times out of ten, if you're writing a WHILE in SQL you're making a mistake.

CodePudding user response：

As the error message is telling, Your subqueries are supposed to return single value. You need to modify below subqueries, to return single scalar value.

==>  (SELECT [NO_ACCOUNT] FROM [transaction].DBO.ML_TRANS1) = '3C' AND (SELECT [TO_ACCOUNT] FROM [transaction].DBO.ML_TRANS1) = '1011')
        
==>          (SELECT [SENDER] FROM [transaction].DBO.ML_TRANS1) <> 'ID')

You can achieve that using multiple ways. some of them are given below. You need to make decision based on your business logic.

Make the query to return single value by adding WHERE clause
Add TOP 1 [ColumnName]