Need query help - 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 2012 Forums

Transact-SQL (2012)

Need query help

Author

Topic

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2014-04-23 : 22:44:32

I want to update table2 by checking table1.

if trauma has atleast 1 then clm2 in table2 would be 1
if infec has atleast 1 then clm2 in table2 would be 2

table1

Clm1 Clm2
Trauma 1
Trauma 1
Trauma 1
Infec 2
Infec 2
Trauma Null

table2

clm1 clm2
Trauma 1
infec 2

I have to update table2 based on table one ny checking multiple columns.

Please help me.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-04-23 : 23:26:36

[code]
declare @table1 table(Clm1 varchar(30), Clm2 int)
insert into @table1
select 'Trauma',1
union all select 'Trauma',1
union all select 'Trauma',1
union all select 'Infec',2
union all select 'Infec',2
union all select 'Trauma',Null

declare @table2 table(clm1 varchar(30), clm2 int)
insert into @table2
select 'Trauma',0
union all select 'infec', 0

update a
set a.clm2 = case when a.clm1 = 'Trauma' then 1 when a.clm1 = 'Infec' then 2 end
from @table2 a
join (
select clm1
from @table1
group by clm1
having count(1) >= 1
)b
on a.clm1 = b.Clm1

select * from @table2
[/code]

WAmin
Starting Member

16 Posts

Posted - 2014-04-23 : 23:27:09

IF EXISTS (SELECT * FROM TABLE1 WHERE Trauma=1) OR EXISTS (SELECT * FROM TABLE1 WHERE infec=1)
UPDATE TABLE2 SET clm2=CASE WHEN EXISTS (SELECT * FROM TABLE1 WHERE Trauma=1) THEN 1 ELSE CASE WHEN EXISTS (SELECT * FROM TABLE1 WHERE infec=1) THEN 2 ELSE clm2 END END

I don't know what are your priorities? Do Trauma should take precedence or infec?
BTW your whole question seems like a riddle, i am not an expert but it is good practice to make a sample table and sample insert to show others what you really want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources