Updating Deptnos based on record count

[barnard90]

Hi

I have an employee Table , ("EMP") with the fields
Empno, Empname, Sal, Deptno and Job

I need to update my table such that I update all the
salaries

The conditon is
Whenever there are more than 100 employees in any department , then the salary of all the employees in those departments should be doubled .

But there is also one condition . In such departments( where count(employee) >100) ) , the employees who already are having the highest salary in those respective departments should not be updated

How do I do that

Please suggest me an UPDATE statement or a series of UPDATE statements

thanks

 

[SantaMufasa]

Barnard,

Is this an assignment for a class?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[barnard90]

No...

It is Data base fix I need to do for my Production Database
in my company
Actually I changed some of the table names for security

 

[SantaMufasa]

Barnard,

In the proof-of-concept, below, to simpify the test data, I have changed your specification from "where count(employee) >100" to "where count(employee) >2". Here are some sample beginning data:

select deptno, sal from emp
order by deptno, sal;

DEPTNO        SAL
------ ----------
    10       1595
    31       1540
    31       1540
    32       1639
    33     1666.5
    34      874.5
    34     1677.5
    35       1595
    41       1034 <-- should double
    41       1320 <-- should double
    41       1540 <-- should double
    41       1595
    42      874.5 <-- should double
    42       1320 <-- should double
    42       1375
    43        825 <-- should double
    43       1210 <-- should double
    43     1457.5
    44        880
    44       1430
    45        946 <-- should double
    45       1210 <-- should double
    45     1437.7
    50       1705
    50    4026.28

Here is code to update the 9 rows that fit your specifications:

update emp set sal = sal * 2
 where deptno in (select deptno from emp group by deptno having count(*)> 2)
   and empno not in (select empno
                       from emp
                      where (deptno, sal) in
                            (select deptno,max(sal)
                               from emp
                              group by deptno)
                    );

9 rows updated.

select deptno, sal from emp
order by deptno, sal;

DEPTNO        SAL
------ ----------
    10       1595
    31       1540
    31       1540
    32       1639
    33     1666.5
    34      874.5
    34     1677.5
    35       1595
    41       1595
    41       2068 <-- Doubled
    41       2640 <-- Doubled
    41       3080 <-- Doubled
    42       1375
    42       1749 <-- Doubled
    42       2640 <-- Doubled
    43     1457.5
    43       1650 <-- Doubled
    43       2420 <-- Doubled
    44        880
    44       1430
    45     1437.7
    45       1892 <-- Doubled
    45       2420 <-- Doubled
    50       1705
    50    4026.28

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[maswien]

or

update emp
set salary = 2 * salary
from
emp inner join 
( select dept, max(salary) as max_sal from emp 
  group by emp_id 
  having count(*) > 100 ) t0 
on emp.dept = t0.dept and emp.salary < t0.salary

 

[maswien]

Please ignore my post, it's SQL server syntax won't work in oracle.