Multiple update statements on same table as a scri

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple update statements on same table as a scri

Author

Topic

ubetman
Starting Member

3 Posts

Posted - 2008-09-17 : 10:52:43

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 tablename set col1='soandso', col2='soandso' where id='soandso'
Update tablename set col1='diff_soandso' , col2='diff_soandso' where id='diff_soandso'
....
....

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 11:13:46

[code]
declare @error int

begin tran

Update tablename set col1='soandso', col2='soandso' where id='soandso'

set @error = @@error

if @error = 0
begin
Update tablename set col1='diff_soandso' , col2='diff_soandso' where id='diff_soandso'
set @error = @@error
end

if @error = 0
commit tran
else
rollback tran[/code]

E 12°55'05.63"
N 56°04'39.26"

ubetman
Starting Member

3 Posts

Posted - 2008-09-17 : 12:03:41

Thanks Peso for your quick reply. I have to update 30 rows like that on the same table with diff data which are independent to each other. So I have to write 30 update statements or is there any work around for that. Thanks alot in advance. For roll back I mean I want to roll back just in case if they want to go back to old data even the data is updated with the new data. Thanks...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 12:11:33

Are you using SQL Server 2005?

If so, you can use the new BEGIN TRY / CATCH functionality.

or try this

Update	tablename
set	col1 =	case id
			when 'soandso' then 'new value here'
			when 'diff_soandso' then 'another new value here'
			else col1
		end,
	col2 =	case id
			when 'soandso' then 'new value here'
			when 'diff_soandso' then 'another new value here'
			else col2
		end
where	id in ('soandso', 'diff_soandso')

E 12°55'05.63"
N 56°04'39.26"

ubetman
Starting Member

3 Posts

Posted - 2008-09-17 : 12:49:35

Thanks Peso...you are very prompt and helpful...
I am little confused of the statements what you wrote..sorry for my ignorance..
Here are my statements to update...

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

Can you use the above statements and wirte what you worte in the previous post with actual data. Thanks for your help...FYI..I am using SQL SERVER 2000.. Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources