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

Question

Post reply

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

agilgautham

SSC Enthusiast

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

#193570

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 6 posts - 1 through 5 (of 5 total)

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

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

Duplicate post.

Replies to the following thread please:

http://qa.sqlservercentral.com/Forums/Topic574925-338-1.aspx

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://qa.sqlservercentral.com/Forums/Topic574926-338-1.aspx

Madhivanan

Failing to plan is Planning to fail

gururajan SSCrazy Points: 2825 More actions · Answer 3

gururajan

SSCrazy

Points: 2825

September 24, 2008 at 2:40 am

#876176

xx

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

gururajan (9/24/2008)
xx

What does this mean?

Madhivanan

Failing to plan is Planning to fail

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

They're kisses - but in this context they can be understood as a big-hearted "Thank you kindly".

Or maybe you just got lucky - better grab your coat JIC 😎

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden