Update Help

[javaguy]

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.

[Veen]

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

[Veen]

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% ..

[Veen]

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