-
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.
-
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
-
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% ..
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|