Update a table with repeating values

[barnard90]

I have an employee table with 3 fields
Empno, DeptNo and DeptFunding .
DeptFunding is actually is the total funding allocated to a department and not a single employee.
Unfortunately the table is already populated with the total department amount and is reflected for each employee

The actual amount for each employee to be displayed is

Deptfunding/ Total number of employees in that department )

The current data is like this

Empno DeptNo DeptFunding
----------------------------------------

101 10 3000
102 10 3000
103 10 3000
104 20 4000
105 20 4000

But it should be like this below


Empno DeptNo DeptFunding
----------------------------------------

101 10 1000
102 10 1000
103 10 1000
104 20 2000
105 20 2000

How can I achieve this with an UPDATE statement . Please suggest

 

[carp]

Try this:

UPDATE my_table o
SET deptfunding = deptfunding/(SELECT count(*) 
                                 FROM my_table i
                                WHERE i.deptno = o.deptno);

Once you ascertain the data is as desired, issue a COMMIT statement.

 

[Beilstwh]

As a matter of table design, if you have a column for total departmental funding, it should be in the Department table and NOT in the employee table.

Bill
Oracle DBA/Developer
New York State, USA