我有一个更新一条记录的查询,只有一条记录.是否有方法可以在同一查询中更新Id,例如插入时选择ScopeIdentity.
UPDATE Task SET MyTime = GetDate(),MyUserId = @userid FROM (select top 1 table where SomeStuff) Select Lastrow that just got updated.
解决方法
根据您的操作,您可能需要使用OUTPUT的表语法.可能是指定临时表/表变量.
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一起使用.
-- 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
