1. The following query will take re

[Tarzan613]

1. The following query will take records from Person & Party tables that are in Production Database and display numbers of the ACTIVE /*in this table 1 means active user..*/users per organization.

select org_id, count (person.party_id) as num_us from production.dbo.person person
inner join
production.dbo.party party on
person.party_id = party.party_id
where curr_stat_type = 1 /*means active user*/
group by org_id


The following query is updating field called ACTIVE_ACCNTS_CNT that in Org_Links table based on the SELECT statement from the query above.


update org_links
SET enduser_cnt = num_us
Where production.dbo.party_id = production.dbo.party_id

Update query that I wrote isn't correct..
Can please somebody tell me how should i write it?
Thanks you

 

[tlbroadbent]

I'm not clear on the relationship between the Org_Links table and the count query. The following query assumes that the relationship is on Org_ID. If this is not the case, you'll need to modify the query.

Update org_links
SET enduser_cnt = qry.num_us
From Org_links org
Join (
(Select org_id, count(*) as num_us
From production.dbo.person
Inner join production.dbo.party party
On person.party_id = party.party_id
Where curr_stat_type = 1
Group by org_id) qry
On org.Org_id=qry.Org_id Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.