Need help from t-sql experts

  • HI,

    I need help..I want the solution for both sql 2000 and sql 2k5...

    I am new to sql server programming. I have to update a table where I have to update different rows with diff data based on primary key. I want to write it in a script instead of multiple update statements updating each row. How can I do that?

    For example:

    update Report_T set Cost_AM = 720.00, Total_Cost_RPS_AM = 830.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=49

    update Report_T set Cost_AM = 432.00, Total_Cost_RPS_AM = 672.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=50

    update Report_T set Cost_AM = 360.00, Total_Cost_RPS_AM = 560.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=51

    update Report_T set Cost_AM = 144.00, Total_Cost_RPS_AM = 224.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=52

    update Report_T set Cost_AM = 216.00, Total_Cost_RPS_AM = 256.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=53

    ....

    ....and so on....

    I want to provide this in a script and also role back script with original data just in case if anything goes wrong. can somebody help me out with this. thanks...

  • Do you have the list of new values in a table? If so, you can do an UPDATE like this:

    UPDATE r SET

    Cost_AM = t.Cost_AM,

    Total_Cost_RPS_AM = t.Total_Cost_RPS_AM,

    EFFECTIVE_DT = GetDate()

    FROM Report_T r

    INNER JOIN SomeOtherTable t ON r.SK_SEQ_ID = t.SK_SEQ_ID

  • if you want the ability to commit or rollback depending if an error occured or not you can do something like this:

    BEGIN TRANSACTION

    update Report_T set Cost_AM = 720.00, Total_Cost_RPS_AM = 830.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=49

    IF @@Error <> 0

    GOTO ErrorHandler

    update Report_T set Cost_AM = 432.00, Total_Cost_RPS_AM = 672.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=50

    IF @@Error <> 0

    GOTO ErrorHandler

    update Report_T set Cost_AM = 360.00, Total_Cost_RPS_AM = 560.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=51

    IF @@Error <> 0

    GOTO ErrorHandler

    update Report_T set Cost_AM = 144.00, Total_Cost_RPS_AM = 224.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=52

    IF @@Error <> 0

    GOTO ErrorHandler

    update Report_T set Cost_AM = 216.00, Total_Cost_RPS_AM = 256.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=53

    IF @@Error <> 0

    GOTO ErrorHandler

    IF @@TranCount > 0

    COMMIT TRANSACTION

    print 'Success!'

    GOTO EndOfScript

    ErrorHandler:

    print 'Error occured...'

    IF @@TranCount > 0

    ROLLBACK TRANSACTION

    EndOfScript:

    but if all you want is run all the updates and rollback then you don't have to worry about all these GOTO and IF @@error.... and can simply write

    BEGIN TRANSACTION

    update Report_T set Cost_AM = 720.00, Total_Cost_RPS_AM = 830.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=49

    update Report_T set Cost_AM = 432.00, Total_Cost_RPS_AM = 672.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=50

    update Report_T set Cost_AM = 360.00, Total_Cost_RPS_AM = 560.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=51

    update Report_T set Cost_AM = 144.00, Total_Cost_RPS_AM = 224.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=52

    update Report_T set Cost_AM = 216.00, Total_Cost_RPS_AM = 256.00, EFFECTIVE_DT=getDate() WHERE SK_SEQ_ID=53

    ROLLBACK TRANSACTION

    in 2005 you can do something much more elegant using TRY...CATCH... but since your code has to be 2000 compatible you can't use that

    as for combining all the update statements into a single statement, it depends where you are getting the new values from. if you happen to have those values in a table then you can do something like...

    update Report_T set

    Cost_AM = t.Cost_AM

    , Total_Cost_RPS_AM = t.Total_Cost_RPS_AM

    , EFFECTIVE_DT = getDate()

    from Report_T r

    inner join TableWithNewValues t on t.SK_SEQ_ID = r.SK_SEQ_ID

    this is assuming you have a table containing the new values and a matching primary key so the inner join above can work... if you don't have such table then feel free to describe where you are getting the new values from and I or someone else will post a solution

    cheers!

  • If you end up having multiple UPDATE statements in your script, it may be easier to use a TRY...CATCH block instead of checking @@ERROR after each UPDATE:

    BEGIN TRY

    BEGIN TRANSACTION

    UPDATE ...

    UPDATE ...

    UPDATE ...

    UPDATE ...

    UPDATE ...

    COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

    IF @@TranCount > 0

    ROLLBACK TRANSACTION

    END CATCH

  • try catch was introduced with 2k5, he needs his script to work with 2000 as well

    otherwise i would agree... try catch is so much cleaner...

  • Triple posted... please click on the user's name to find the other 2.

    This is a huge waste... splits the answers to a single problem into 3 different posts. Please don't cross post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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