DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Update Help

  1. #1
    Join Date
    Jul 2003
    Location
    Texas
    Posts
    3

    Question Update Help

    Hi All,

    I have an employee_group table that looks like this:

    group_id, employee_id, group_cnt
    10, 1, 0
    10, 2, 0
    20, 3, 0

    I want to update the group_cnt column so that it would contain the number of employees in the group. So after the update, the results would look like this:

    group_id, employee_id, group_cnt
    10, 1, 2
    10, 2, 2
    20, 3, 1

    I've done this type of thing in SQL Server, but the syntax I used doesn't work in Oracle. I'm not sure how to join to the table to update it once I've got my count.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Posts
    16
    Try this -> Not sure this works
    update employee_group a
    set a.group_cnt = ( select G_CNT, group_id from (select count(group_cnt) G_CNT, group_id from employee_group
    group by group_id ) b
    where a.group_id = b.group_id

  3. #3
    Join Date
    Aug 2003
    Posts
    16
    Oops Ignore the last one ==>
    Here is the SQL statement

    UPDATE employee_group a
    SET group_cnt = ( SELECT count(group_cnt)
    FROM employee_group
    WHERE a.group_id = b.group_id);


    This work 100% ..

  4. #4
    Join Date
    Aug 2003
    Posts
    16
    Real Test ::

    SQL> select * from empgrp;

    GID EMP_ID CNT
    ---------- ---------- ----------
    10 1 0
    10 2 0
    20 1 0

    SQL> update empgrp a
    2 set cnt = ( select count(cnt) from empgrp b where a.gid = b.gid);

    3 rows updated.

    SQL> select * from empgrp;

    GID EMP_ID CNT
    ---------- ---------- ----------
    10 1 2
    10 2 2
    20 1 1

Posting Permissions

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


Click Here to Expand Forum to Full Width