Return the record modified by UPDATE TOP 1

I have a C#开发者_高级运维 application and would like to return the record updated by the TSQL UPDATE TOP 1 without doing a 2nd query. Is this possible?

You can use OUTPUT, for example:

DECLARE @tmp TABLE (Id int not null)
UPDATE TOP (1) [YourTable]
SET [YourColumn] = newValue
OUTPUT inserted.Id INTO @tmp

SELECT * FROM @tmp

(add more columns to suit)

Note the INTO here is necessary in the general case to avoid issues with triggers; otherwise it is common to see:

The target table 'YourTable' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

You can use the output clause.

update top (1) T
set Col = 'x'
output inserted.*
from YourTable as T

Yes. Its possible .

DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE() 
OUTPUT inserted.BusinessEntityID,
   deleted.VacationHours,
   inserted.VacationHours,
   inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO 
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

refer : http://msdn.microsoft.com/en-us/library/ms177523.aspx#CaptureResults