updating a table conditionally with values from a group by sub query in oracle
- by user333147
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
1) 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 ;
2)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;
