two queries - one cfquery call

[alwaysconfused]

I'm trying to do the following in one single cfquery call. However, I'm having
trouble referencing "memberCount" in the Insert part of the statement. It
keeps saying invalid column name "memberCount".

How would I reference it correctly? Thanks.

<cfquery>
SELECT COUNT(1) AS memberCount
FROM groupMembers
WHERE groupId = #arguments.groupId#;

UPDATE groupNames
SET groupNumberOfMembers = memberCount
WHERE groupId = #arguments.groupId#;
</cfquery>

[mpwoodward *TMM*]

alwaysconfused wrote:

> I'm trying to do the following in one single cfquery call. However, I'm having
> trouble referencing "memberCount" in the Insert part of the statement. It
> keeps saying invalid column name "memberCount".
>
> How would I reference it correctly? Thanks.
>
> <cfquery>
> SELECT COUNT(1) AS memberCount
> FROM groupMembers
> WHERE groupId = #arguments.groupId#;
>
> UPDATE groupNames
> SET groupNumberOfMembers = memberCount
> WHERE groupId = #arguments.groupId#;
> </cfquery>
>
>

I think you'd have to do something like this--this is assuming SQL Server:

DECLARE @memberCount int
SELECT @memberCount = COUNT(1)
FROM groupMembers
WHERE groupId = #arguments.groupId#

UPDATE groupNames
SET groupNumberOfMembers = @memberCount
WHERE groupId = #arguments.groupId#

I can't test that at the moment but I think that should get you rolling.

Matt
--
Matt Woodward
[email]mpwoodward@gmail.com[/email]
Team Macromedia - ColdFusion

[Ali Soylu]

How about:
UPDATE groupNames
SET groupNumberOfMembers = (SELECT COUNT(1) FROM groupMembers WHERE
groupID=#arguments.groupId# )
WHERE groupID = #arguments.groupId#;

or evenbetter , if you wanna do all groups:
UPDATE groupNames
SET groupNumberOfMembers = (SELECT COUNT(1) FROM groupMembers WHERE
groupMembers.groupID =groupNames.groupID )


ALi

[Dan Bracuk]

Here is my suggestion
<cfquery name="abc">
alter table groupnames
drop column groupnumberofmembers
</cfquery>

<cfquery>
select groupname count(groupname) as membercount
from groupmembers join groupnames using (groupid)
where groupid = #arguments.groupid#
group by groupid
</cfquery>

syntax varies with different databases.