时间戳

首页 > 解决方案 > SQL 更新 WHERE IN 子查询已停止工作

sql - SQL 更新 WHERE IN 子查询已停止工作

问题描述

我有这张桌子。（还有其他字段，但 Id 和 Lang 在这里很重要。）

CREATE TABLE Request 
(
    Id int IDENTITY(1,1) NOT NULL,
    Lang nvarchar(20) NULL
    CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (Id ASC)
) ON [PRIMARY]

我有一个用户定义的表类型。

CREATE TYPE dbo.IntList AS TABLE 
(
    Item int NOT NULL
)

最后，我有一个存储过程，它为我的表类型接收一个变量，以更新所有指定 Id 的 Lang 字段。

CREATE PROCEDURE BulkUpdLang
    @ids as IntList READONLY,
    @lang as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Request
    SET Lang = @lang
    WHERE Id IN (SELECT Item FROM @ids);
END

这工作正常，由于某种原因，已经停止。它现在因错误而爆炸：

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的

假设 MyTable 有 10 个 ID 为 1-10 的项目，这是我尝试过的：

declare @ids IntList;
insert into @ids
    select 3 union all
    select 4 union all
    select 5;

-- This is how I test the sproc
-- EXEC BulkUpdLang @ids, 'en-us';

-- This is just a select and works just fine.
--Select * from Request
--    Where Id in (Select Item From @ids);

-- This WAS working, but now throws the subquery error.
Update Request
Set Lang = 'en-us'
Where Id in (Select Item From @ids);

-- This works as expected. Unfortunately, the Ids need to be a variable.
--Update Request
--Set Lang = 'en-us'
--Where Id in (3, 4, 5);

-- This also throws the subquery error.
--Merge Request as Req
--Using @ids as Ids -- "Using (Select Item From @ids) as Ids" changes nothing
--on Req.Id = Ids.Item
--when matched then
--update set
--    Req.Lang = 'en-us';

-- This also throws the subquery error.
--Update Request
--Set Lang = 'en-us'
--From (Select Item From @ids) Ids
--Where Id = Ids.Item;

让我发疯的是，这是有效的。我倾向于相信某些 SQL 服务器设置已经改变了它处理子查询的方式。有没有人有任何想法？我快疯了！

此表上还有一个触发器：

ALTER TRIGGER [dbo].[Request_Status_Audit]
    ON [dbo].[Request]
    AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @oldStatus nvarchar(15) = (SELECT [Status] FROM Deleted);
    DECLARE @newStatus nvarchar(15) = (SELECT [Status] FROM Inserted);
    
    IF (@oldStatus <> @newStatus) BEGIN
        INSERT INTO dbo.Audit_RequestStatus (RequestId, StatusDate, StatusValue)
            SELECT Inserted.Id, GETDATE(), Inserted.[Status]
            FROM Inserted;
    END

    -- data retention
    DELETE FROM dbo.Audit_RequestStatus
    WHERE StatusDate < DATEADD(YEAR, -6, GETDATE());

END

标签： sqlsql-servertsql

解决方案

您的触发器不处理多行插入和更新（也不处理 0 行），正如Brent Ozar（我在大多数触发器中发现的静默错误）和其他人所记录的那样。

CREATE OR ALTER TRIGGER [dbo].[Request_Status_Audit]
    ON [dbo].[Request]
    AFTER INSERT, UPDATE
AS 

    SET NOCOUNT ON;

    IF NOT UPDATE([Status]) OR NOT EXISTS
        (
            SELECT Id, [Status]
            FROM inserted
            EXCEPT
            SELECT Id, [Status]
            FROM deleted
        )
        RETURN ;
    
    INSERT INTO dbo.Audit_RequestStatus (RequestId, StatusDate, StatusValue)
        SELECT i.Id, GETDATE(), i.[Status]
        FROM (
            SELECT Id, [Status]
            FROM inserted
            EXCEPT
            SELECT Id, [Status]
            FROM deleted
        ) i;

    -- data retention
    DELETE FROM dbo.Audit_RequestStatus
    WHERE StatusDate < DATEADD(YEAR, -6, GETDATE());

GO

笔记：

  • 比较必要的列inserted并deleted查看是否有更改，还要检查UPDATE函数（请注意，UPDATE它只告诉您查询中是否存在该列，而不是数据是否实际更改），如果可以，请尽早退出。
  • 在您INSERT再次检查更改的列
  • 我强烈建议您将其转移DELETE到代理工作中。

推荐阅读