Need help from t-sql experts

Question

Post reply

Need help from t-sql experts

ubet_2004

SSC Veteran

Points: 231
More actions
September 17, 2008 at 1:44 pm

#193023

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...

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

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

Harveysburger SSCommitted Points: 1562 More actions · Answer 2

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!

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

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 ...

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@TranCount > 0

ROLLBACK TRANSACTION

END CATCH

Harveysburger SSCommitted Points: 1562 More actions · Answer 4

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...

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

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)