Multiple update statements in a script

Question

Multiple update statements in a script

ubet_2004

SSC Veteran

Points: 231
More actions
September 17, 2008 at 12:08 pm

#212710

HI,
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 8 posts - 1 through 7 (of 7 total)

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

Chad Crawford SSChampion Points: 11610 More actions · Answer 1

Is the cost information stored in another table somewhere where you could link it up using a join? If not, you could use a case statement to bring it all into one statement, but that would be pretty ugly. Can you use transactions as your "rollback" in case something goes wrong, or do you have other long-term rollback reqs?

Chad

ubet_2004 SSC Veteran Points: 231 More actions · Answer 2

Thank you for your reply. Its stored in the same table. can you provide an example of using transaction with update statements? Thanks...

Chad Crawford SSChampion Points: 11610 More actions · Answer 3

Look up BEGIN TRANSACTION in books online for details and options for using transactions.

So for this specific example:

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

The "720.00" and the "830.00" are also stored in Report_T? They are there for a different SK_SEQ_ID? I know the old values will be there, but is Report_T also where the new values come from?

Chad

ubet_2004 SSC Veteran Points: 231 More actions · Answer 4

I was given a spreadsheet with new data. My task is to write a script to update two columns on the table with new data. The script will be run by diff team in stage server and then in production. And also I have to provide role back script with original data. Thanks for your replies.

Chad Crawford SSChampion Points: 11610 More actions · Answer 5

Then I think you can either upload the spreadsheet data into a table and do it all in one update query using a join, or create individual update for each row. If it sounds complex to upload and join the table, just create an update statement for each row - it will be quick and easy.

For your rollback script, use a transaction if you will know immediately after you run the script if all is "OK", since select queries will be blocked until the transaction is committed. If not, create an archive copy of the table (or just the impacted rows) so you have the historical information you need to bring the data back should something need to be undone.

I guess another option is to keep a backup of the database that you can restore with a different name (mydatabase_1 or whatever) and then do your rollback by comparing the records from the table in the live database and the restored database. That would keep the extra table out of the database and it maybe a little more clean.

Chad

Jeffrey Williams SSC Guru Points: 90345 More actions · Answer 6

Step 1: Import Excel spreadsheet into SQL Server

Step 2: Write update script to use data in imported table

Basic structure of the script would be something like:

BEGIN TRANSACTION;

SELECT * INTO dbo.Report_T_Backup FROM dbo.Report_T -- backup current table

UPDATE dbo.Report_T

SET Cost_AM = e.Cost_AM

,Total_Cost_RPS_AM = e.Total_Cost_RPS_AM

,Effective_DT = current_timestamp

FROM dbo.Report_T t

JOIN dbo.Imported_Table e ON e.SK_SEQ_ID = t.SK_SEQ_ID;

ROLLBACK TRANSACTION; -- change this to COMMIT TRANSACTION once you know it works

Now, if you need to rollback the changes after the script is run (and committed) - you can use the backup table.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004589 More actions · Answer 7

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)

Multiple update statements in a script

Cookies on SQLServerCentral