Post reply

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

  • 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

  • September 24, 2008 at 1:14 am

    #876146

    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

  • September 24, 2008 at 1:18 am

    #876149

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

    Madhivanan

    Failing to plan is Planning to fail

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

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