If you want update a table with top clause see the below script:
UPDATE top (1) TABLE1
SET status ='ready'
where status ='hold'
At the same time if you want to return the Row IDs of updated table:
UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid
where status ='hold'
—————— result—————-
rowid
1
Same thing if you want to store in variable
DECLARE @UpdatedIds table (id int)
DECLARE @rowId int
UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid INTO @UpdatedIds
where status ='hold'
SELECT @rowId=id FROM @UpdateIds
SELECT @rowId
Filed under: UPDATE with top 1 | Tagged: how to return updated rowid, Tips, tricks, update with top clause |
Leave a comment