How to update and select the updated records in a single T-Sql statement

Question

How to update and select the updated records in a single T-Sql statement

agilgautham

SSC Enthusiast

Points: 133
More actions
September 24, 2008 at 12:52 am

#198385

Select * from customer
CustomerID CustomerName
----------- --------------
1 TestC1
2 TestC2
3 TestC3
4 TestC4
select * from CustomerTransact
TransID CustomerID TransName Status
--------- ------------- -------- -------
1 1 Trans1 A
2 2 Trans2 A
3 3 Trans3 A
4 4 Trans4 A
Stored procedure to update n number of recordsof customertransact and output that customer record.
Create proc pUpdateCust(@RecToUpdate AS INT)
As
BEGIN
UPDATE TOP(@RecToUpdate) CustomerTransact
SET STATUS = 'D'
OUTPUT Deleted.TransID,
CUSTOMERNAME
FROM CUSTOMER
INNER JOIN CustomerTransact
on CustomerTransact.customerid = customer.customerid
WHERE CustomerTransact.status = 'A'
END
EXEC pUpdateCust 2
OUTPUT the updated customer record 1,2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Have a look at the OUTPUT clause. If I'm understanding you correctly, it'll do what you need.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Duplicate of http://www.sqlservercentral.com/Forums/Topic574926-338-1.aspx

Madhivanan

Failing to plan is Planning to fail