获取 SQL Server 中最后更新行的标识

2022-01-17 00:00:00 sql-update sql-server stored-procedures

@@IDENTITY返回最后一行插入的ID，我要检索最后一行更新的ID.

@@IDENTITY returns the ID of the last row inserted, I want to retrieve the ID of the last row updated.

这是我的查询:

UPDATE [Table] 
SET Active = 1, 
    Subscribed = 1, 
    RenewDate = GETDATE(),
    EndDate = DATEADD(mm,1,getdate()),
WHERE SC = @SC
  AND Service = @Ser

如何获取此更新行的 ID?

How do I get the ID of this updated row?

该列名为 TableID，我没有在查询中使用它.

The column is called TableID and I'm not using it in the query.

推荐答案

因为没有插入ID，所以无法检索ID.....

You cannot retrieve an ID since there is no ID being inserted.....

但你可以:

  1. 只需使用与 UPDATE 中相同的条件查询表:

  1. just query the table using the same criteria as in your UPDATE:

SELECT TableID 
FROM dbo.Table
WHERE SC = @SC AND Service = @Ser  -- just use the same criteria

  • 使用 UPDATE 上的 OUTPUT 子句获取该信息:

  • use the OUTPUT clause on the UPDATE to get that info:

    UPDATE [Table] 
SET Active = 1, 
    Subscribed = 1, 
    RenewDate = GETDATE(),
    EndDate = DATEADD(mm,1,getdate())
OUTPUT Inserted.TableId       -- output the TableID from the table
WHERE SC = @SC AND Service = @Ser

    • 详细了解 Technet 上的 OUTPUT 子句 - 它也可以在 INSERT 和 DELETE 上使用

    相关文章