获取更新行 [英] Get Updated Row

问题描述

我有一个更新一条记录的查询，而且只有一条记录.有没有办法在插入时在同一查询中更新 Id，例如 Select ScopeIdentity.

I have a query that updates one record, and only one record. Is there are way to get the Id updated in the same query such as Select ScopeIdentity when inserting.

UPDATE Task
SET MyTime = GetDate(), MyUserId = @userid
FROM (select top 1 table where SomeStuff)

Select Lastrow that just got updated.

推荐答案

根据您在做什么，您可能需要使用 OUTPUT 的表格语法.一种可能性是指定一个临时表/表变量.

Depending on what you are doing, you may need to use the table syntax of OUTPUT. A possibility is to specify a temporary table / table variable.

DECLARE @T TABLE
(
    MyID INT NOT NULL
)

UPDATE Task
SET MyTime = GetDate(), MyUserId = @userid
OUTPUT INSERTED.MyID INTO @T
FROM (/* your FROM clause here */) Task

gbn 在我之前得到了一个编辑，基本上和上面说的一样.我想补充一点，另一种方法是先获取 ID，然后按 ID 更新.此外，TOP 1 应该几乎总是与 ORDER BY 一起使用.

gbn got an edit in ahead of me that essentially says the same thing as above. I would like to add that another way to do this is to grab the ID first and then update by ID. Also, TOP 1 should almost always be used with an ORDER BY.

-- You may need to clean up the error handling. I just wanted
-- to put something simple in to remind that it is necessary.
DECLARE @userid INT; SET @userid = /* e.g., */ 1234
BEGIN TRANSACTION
IF @@ERROR <> 0 RETURN
DECLARE @TaskID INT
SET @TaskID = (SELECT TOP 1 TaskID FROM Task WITH (UPDLOCK) ORDER BY /* e.g., */ TaskID) -- TaskID should be the PK of MyTable. Must be unique.
IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN END
UPDATE Task
SET MyTime = GETDATE(), MyUserId = @userid
WHERE TaskID = @TaskID
COMMIT TRANSACTION

这篇关于获取更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！