Update using count and inner join

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

Update using count and inner join

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-03-22 : 06:10:42

Hi all,

I have 2 field(Adult and children) in policy table.
I want update using 'ageabove18' field from traveller table
In traveller table, i have row wise data.If a policy has 3 traveller with age 22,5,6 then the data be :-

policyid age Ageabove18 (in traveller table)
10000 22 Y
10000 5 N
10000 6 N

Expected Result(after updation in policytable)
Policyid Adult Children
10000 1 2

I want to know,How to use inner join and count in an update statment.Any help would be highly appreciated.Many Thanks.

Regards,
SG

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-22 : 06:24:15

One way:

Create table #Policy
(PolicyID int,
age int,
Ageabove18 Char(1)
)

Insert into #Policy
Select 10000,22,'Y' union all
Select 10000,5,'N' union all
Select 10000,6,'N' union all
Select 10001,22,'Y'

Create table #Traveller
(PolicyID int,
Adult int,
Children int)

Insert into #Traveller
Select 10000, null,null union all
Select 10001, null,null

Update #Traveller
set Adult = st.adult,
Children = st.children
from #Traveller T inner join (Select PolicyID, Sum(Case when Ageabove18 ='Y' then 1 else 0 end) as Adult,
Sum(Case when Ageabove18 ='N' then 1 else 0 end) as children From #Policy T1 group by PolicyID ) as st
on T.PolicyID = st.PolicyID

Select * from #Traveller

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-22 : 06:25:13

Try this -


UPDATE P SET Adult = Adults, Children = A.Children
FROM Policy P
INNER JOIN
(
SELECT	PolicyID, 
		SUM( CASE WHEN Ageabove18 = 'Y' THEN 1 ELSE 0 END ) AS Adults,
		SUM( CASE WHEN Ageabove18 = 'N' THEN 1 ELSE 0 END ) AS Children
FROM	Traveller 
GROUP BY policyid 
) A ON P.Policyid = A.policyid

Vaibhav T

If I cant go back, I want to go fast...

satheesh
Posting Yak Master

152 Posts

Posted - 2011-03-22 : 09:12:37

Thanks you all Its working..

abbott
Starting Member

1 Post

Posted - 2011-03-28 : 15:50:14

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources