Server 2008 (SP4)子查询返回的值超过一个

Question

关于server 2008 (SP4)的此查询直到昨天上午才正确运行。

今天我又犯了一个难以理解的错误。

我需要替换Server表中的值';' to '-' of列RisInt和RisNot。

我尝试过使用SQL语法UPDATE忽略，但没有成功。

Err 42000 - SQL ServerIncorrect语法

如何解决这个问题？

你能帮帮我吗?

提前感谢您的帮助，真的很感激。

下面的SQL代码。

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.

#编辑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

Answer 1

选择@IGI =(从a.IGXF a中选择DBO.Details，在其中删除a.IGXF = i.IGXF) 从DBO.SUPPORT中删除IGBA = @IGI

编写触发器时所犯的一个常见错误是假定单个行受到影响。当返回多行时，上面的标量子查询将失败。

下面是一个未经测试的示例，说明如何重构触发器代码以处理多个行。这也使用ANSI-92 SQL样式联接，而不是旧的语法。

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