CODEWITHSUNDEEP
sql-server
Antonio Mailtraq
Antonio Mailtraq

Reputation: 1407

SQL Server 2008 (SP4) Subquery returned more than 1 value

This query on SQL server 2008 (SP4) has worked correctly until yesterday morning.

Today returns an incomprehensible error for me.

I need replace the values ';' to '-' of column RisInt and RisNot in my SQL Server table.

I have tried with SQL syntax UPDATE IGNORE without success.

[Err] 42000 - [SQL Server]Incorrect syntax near

How to do resolve this?

Can you help me?

Thank you in advance for any help, really appreciated.

My SQL code below.

UPDATE [DBO].[Details]
SET RisInt = REPLACE(RisInt, ';', '-'),
 RisNot = REPLACE(RisNot, ';', '-')

[Err] 21000 - [SQL Server]Subquery returned more than 1 value. 
This is not permitted when the subquery follows =, !=, <, <= , >, >= or
when the subquery is used as an expression.
01000 - [SQL Server]The statement has been terminated.

#Edit 01

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @IGI VARCHAR(20)

SELECT @IGI = (SELECT a.IGXF FROM DBO.Details a, DELETED i
WHERE a.IGXF = i.IGXF)
    DELETE FROM DBO.SUPPORT where IGBA = @IGI

    -- Insert statements for trigger here

END

Upvotes: 0

Views: 57

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

SELECT @IGI = (SELECT a.IGXF FROM DBO.Details a, DELETED i WHERE a.IGXF = i.IGXF)

DELETE FROM DBO.SUPPORT where IGBA = @IGI

A common mistake made when writing triggers is to assume a single row is affected. The scalar subquery above will fail when more than one row is returned.

Below is an untested example of how the trigger code can be refactored to handle multiple rows. This also uses ANSI-92 SQL style joins rather than the old syntax.

SET NOCOUNT ON;
DELETE FROM dbo.SUPPORT
WHERE IGBA IN(
    SELECT a.IGXF
    FROM dbo..Details AS a
    JOIN DELETED  AS i ON
        a.IGXF = i.IGXF
    );

Upvotes: 1

Related Questions