Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    21

    Unanswered: UPDATE multiple rows

    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
    Last edited by rmill9681; 10-06-04 at 14:58.

  2. #2
    Join Date
    Sep 2004
    Posts
    21
    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???

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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 ...

  4. #4
    Join Date
    Sep 2004
    Posts
    21
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •