SQL Server - 子查询返回的值超过1个

Question

我已经阅读了一些关于它的帖子，但我完全不理解。

在我来说，我有一个表，让我们说“父”有两列ID，DELETED。

ID是int，DELETED是一个位列。

我有另一个表，让我们说'子'具有列ID，ID_PARENT，NAME，DELETED作为父表。

ID和ID_PARENT是INT 名称是nvarchar的 删除的有点

我创建了一个触发器上表父更新其更新子表：

UPDATE 
     [dbo].[Child] 
    SET 
     [DELETED] = I.[DELETED]   
    FROM 
     INSERTED I INNER JOIN [dbo].[Child] AS c 
     ON I.ID = c.ID_PARENT 
WHERE 
     c.[NAME] IS NOT NULL; 

请记住，一个父可能有很多孩子，一个孩子只有一个父母。

所以，当我做下面的查询：

UPDATE [dbo].[Parent] 
    SET DELETED = 1 
    WHERE ID IN (100, 200); 

当然，这会影响到很多行。我得到以下错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

虽然您已经解决了您的问题。在评论部分中，您提到您触发了创建的子项。但对于其他社区用户来说，这里是通过解决方案完成场景创建

CREATE TABLE TempParent 
(
    ID INT, 
    DELETED BIT 
) 
GO 
CREATE TABLE TempChild 
(
    ID INT, 
    ID_PARENT int, 
    NAME varchar(100), 
    DELETED BIT 
) 

INSERT INTO TempParent 
SELECT 
    100,0 UNION ALL 
SELECT 
    200,0 UNION ALL 
SELECT 
    300,0 UNION ALL 
SELECT 
    400,0 
GO 
INSERT INTO TempChild 
SELECT 
    100,100,'Rahul',0 UNION ALL 
SELECT 
    200,200,'Rajesh',0 UNION ALL 
SELECT 
    300,300,'Anil',0 UNION ALL 
SELECT 
    400,400,NULL,0 

GO  
CREATE TRIGGER dbo.tr_TempParent 
ON dbo.TempParent 
FOR UPDATE 
AS 
BEGIN 
    SET NOCOUNT ON; 
    UPDATE 
     [dbo].TempChild 
    SET 
     [DELETED] = I.[DELETED]   
    FROM 
     INSERTED I INNER JOIN [dbo].TempChild AS c 
     ON I.ID = c.ID_PARENT 
WHERE 
     c.[NAME] IS NOT NULL; 
END 
GO 
SELECT * FROM TempParent 

SELECT * FROM TempChild 

UPDATE [dbo].TempParent 
SET DELETED = 1 
WHERE ID IN (100, 200); 

DROP TABLE TempParent 
DROP TABLE TempChild