CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-update
Ramkumar
Ramkumar

Reputation: 143

updating a table conditionally with values from a group by sub query in oracle

the problem is Update the salary of all the employees by 50% who had worked on 5 or more than 5 projects, by 30% (>= 3 projects), by 20 % (>= 1 projects) the number of project is got by performing a group by query on the EMPLOYEE_PROJECT_HISTORY;

i have tried these queries

update emp set emp.sal=
case 
when jemp.pcount >=5 then emp.sal+ (emp.sal*50)/100
when jemp.pcount >=3 then emp.sal+ (emp.sal*30)/100
when jemp.pcount >=1 then emp.sal+ (emp.sal*20)/100
else emp.sal+ (emp.sal*20)/100
end 
from employee emp join (select empno as jempno,count(projectno) as pcount from EMPLOYEE_PROJECT_HISTORY by empno) jemp on emp.empno=jemp.jempno ;


update employee a set a.sal= case (select count(b.projectno) as pcount from EMPLOYEE_PROJECT_HISTORY b group by b.empno ) 
    when b.pcount >5 then
    a.sal = a.sal+ (a.sal*50)/100
    when pcount >3 then
    a.sal = a.sal+ (a.sal*30)/100
    when pcount >1 then
    a.sal = a.sal+ (a.sal*20)/100
    end;

Upvotes: 2

Views: 2946

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You won't be able to update the join if it contains a GROUP BY clause in Oracle. You can use an inline subquery, like this:

UPDATE employee e
   SET sal = sal * (SELECT CASE
                              WHEN COUNT(*) >= 5 THEN
                               1.5
                              WHEN COUNT(*) >= 3 THEN
                               1.3
                              WHEN COUNT(*) >= 1 THEN
                               1.2
                              ELSE
                               1
                           END
                      FROM employee_project_history eph
                     WHERE eph.empno = e.empno);

Upvotes: 2

Related Questions