Im trying to update a table based on a count from another table (using a join). Anyone good with sql that could help me understand how to do this? thanks
SQL> CREATE TABLE X (KNT NUMBER); Table created SQL> SELECT COUNT(*) FROM Y; COUNT(*) ---------- 4781 SQL> SQL> INSERT INTO X 2 SELECT COUNT(*) FROM Y; 1 row inserted SQL> SQL> SQL> SELECT * FROM X; KNT --- 4781 I am not sure if this what you want happen
sorry ... I did not read your question very well, this is an example of SQL updating count based on the result of two tables join together ... SQL> update x 2 set knt = (select count(*) from 3 a , b 4 where a_pidm = b_pidm); 1 row updated SQL> commit; Commit complete SQL> select * from x; KNT ----------- 34065
The setup of my code is suppose to go something like this update groups g1 set NumMems = (select count(...) from groups as g2, members as m where ...) groups is gid, gname, and Num Mems members table has member information and groupid # which is what I am trying to count. There are like 10 groups and each has a certain amount of people in each. I just want to be able to join the two tables, and insert the counts for each group into NumMems
I donno if I'm understanding you correctly, this work? update groups g1 set g1.NumMems = (select count(*) from members as m where m.groupid = g1.groupid group by groupID)
gid the unique definition of a group in the groups table in the members table it is called groupid and it shows that for each member. Im trying to count those in the members table while joining it to the groups.gid = members.groupid
Yeah, this should work. Ignore my last post, it is incorrect syntax Update groups set NumMems = (select count(*) from members where members.groupid = groups.groupid group by groupid ) This should work. It will get a count of each groupid in the members table and insert it inthe groups table for each groupid
dont you mean 'members.gid' As I mentioned GID is the name of the field in the members table, so in that case do i just change it to that and it should be OK?
actually I meant groups.gid b/c groups.gid = members.groupid right? Update groups set NumMems = (select count(*) from members where members.groupid = groups.gid group by groupid )
Update groups set NumMems = (select count(*) from members where members.groupid = groups.gid group by groupid) sorry gid is actually in groups, and groupid is in members, so i did it that way!
you familiar with triggers -- so that the groupid column can maintain the correct values on update....?
I know a little about triggers... What is that that you want to do, update the memebers.groupid column when an update occurs?
I want to write a trigger that maintains the correct values of NumMems on update (of the groupid column in members table), or on insert or delete in the members table. After updating, deleting or inserting a command it may affect more than one row. I need to test a trigger by inserting rows in the members table, deleting rows from the members table, updating a row by changing the groupid.
Since it's doing a count, you'll only have to recalc the NumMems after an insert occured in the members table. An update shouldn't affect the count. You can create the following trigger CREATE TRIGGER UpdateNumMems ON members After Insert AS Update groups set NumMems = (Select count(*) from members where groupid= Inserted.groupid) from Inserted where groups.gid= Inserted.groupid
Is the update and deleting factored in? I ran that code and it said it did it succesfully, but what did do exactly, do I need to TEST by trying to add members?
My bad, i forgot about delete, as that would affect the count as well. Since the update will change the groupid, then you'll also have to create a update trigger. I wrongly assumed it was part of a key, so it wouldn't change. I personally like to create an seperate trigger for update/delete/insert. CREATE TRIGGER MembersDelete ON members After Delete AS Update groups set NumMems = (Select count(*) from members where groupid= Deleted.groupid) from Deleted where groups.gid= Deleted.groupid The update trigger would be the same, except with the update key word. I think it uses the inserted alias like an insert. You can see if it actually works by inserting/updating/deleting records in the member table. Then check the groups table to see if there is an accurate count.
