I need to figure out how to update a column NumMembers with the current count of members.

I know I use this

UPDATE groups
SET NumMembers =

but how do I insert my count code below into this?


I have a query that counts the number of members in a table

first I create this view

create view myview
as
SELECT lname, fname, gname
FROM groups g, members m
WHERE g.gid=m.groupid

then

SELECT x.gname, count(*) as Total
FROM myview x, myview y
WHERE x.gname=y.gname and x.lname=y.lname
GROUP BY x.gname

I did this

CREATE VIEW myview
AS
SELECT groupid, count(*)as Total
FROM members m
GROUP BY groupid

UPDATE groups
SET NumMems=Total
FROM myview v, groups g
WHERE g.gid=v.groupid


and it worked...except, one of the groups has no members, and it returned 1 for a count. ANYONE KNOW WHY???

It worked, true, but it did NOT do what you wanted. View doesn't contain number of members per group (what was your goal), but counts group appearances in the table. That's why it returned 1.

It would help if you described tables you use ("members" and "groups"). Now we can only guess ... is it

CREATE VIEW myview AS
SELECT groupid, sum(number_of_members) AS total
FROM members
GROUP BY groupid;

or maybe

CREATE VEIW myview AS
SELECT groupid, COUNT(*) AS total
FROM members
WHERE number_of_members > 0
GROUP BY groupid;

or something else ...

sorry.

groups contains
gid
gname
NumMembers
ex: 00001 fishing NULL

members contains
lname
fname
gid

ex: Rob Miller 0001

I need to count the number of members in each group and update the Num Members column for each group (as of now, it is null)

hope this is enough info