Select Union and Update - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select Union and Update

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-11 : 12:41:09

This is my query:


Select col1,col2,col3 from TblA where condition1
union all
Select col4,col5,col6 from TblA where condition2
union all
select col7,col8,col9 from TblA where condition3

Update TblA set col10 = somevalue where Condition1
Update TblA set col10 = someothervalue where Condition2
Update TblA set col10 = anothervalue where Condition3

I need to run my first update query when my first select statement returns some value, the same with the other 2 select and updates

I tried this:


Declare @rc int
Select col1,col2,col3 from TblA where condition1
set @rc = @@rowcount
If(@rc >0 )
Begin
Update TblA set col10 = somevalue where Condition1
End 
Union all
Declare @rc int
Select col4,col5,col6 from TblA where condition2
set @rc = @@rowcount
If(@rc >0 )
Begin
Update TblB set col10 = somevalue where Condition2
End 
Union all
Declare @rc int
Select col7,col8,col9 from TblA where condition3
set @rc = @@rowcount
If(@rc >0 )
Begin
Update TblB set col10 = somevalue where Condition3
End

This gives me error. Any other way to do this?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2005-05-12 : 05:56:18

I dont know this will help you.. Please have a try

create table TblA(Col1 int,
Col2 int,
Col3 int,
Col4 int,
Col5 int,
Col6 int,
Col7 int,
Col8 int,
Col9 int,
Col10 int)

insert into TblA values(1,2,3,4,5,6,7,8,9,10)
insert into TblA values(11,12,13,14,15,16,17,18,19,20)
insert into TblA values(21,22,23,24,25,26,27,28,29,30)

Select col1,col2,col3 from TblA where col1=4
union all
Select col4,col5,col6 from TblA where col2=2
union all
select col7,col8,col9 from TblA where col3=3

Update TblA set col10 = 40 where col1=1
and 1=(Select count(*)/case count(*) when 0 then 1 else count(*) end from TblA where col1=4)

Update TblA set col10 = 50 where Condition2
Update TblA set col10 = 60 where Condition3
------------------

Work smarter not harder take control of your life be a super achiever

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources