Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to get the actual results of SQL UPDATE command (ie the table rows themselves), not just get the number of rows (other than by converting the UPDATE command to a SELECT)? I'm using mainly Postgres and Informix, but I'd be interested to hear about other database systems that have this feature, or perhaps a particular client that supports this.

share|improve this question
3  
OUTPUT clause –  Remus Rusanu Apr 11 '12 at 21:54

1 Answer 1

up vote 3 down vote accepted

Here's how you'd do something like that in SQL Server. Because an UPDATE is essentially old data and new data, SQL Server exposes the deleted and inserted dynamic tables. You can work with them to get what the data looked like prior to the UPDATE, and after the UPDATE:

update HumanResources.Department
set GroupName = 'Testing Group Name'
output
    deleted.DepartmentID as Old_DepartmentID,
    inserted.DepartmentID as New_DepartmentID,
    deleted.Name as Old_Name,
    inserted.Name as New_Name,
    deleted.GroupName as Old_GroupName,
    inserted.GroupName as New_GroupName
where DepartmentID > 12
share|improve this answer
    
Thanks, and now I see there's also something similar in Postgres, the RETURNING clause. –  newenglander Apr 12 '12 at 5:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.