Go Back   Wiki NewForum | Latest Entertainment News > Career Forum & Tips > Tech Forum & Tutorial > Oracle Database, SQL, Application, Programming


Update statements in Query I have one query, Update the salary of the employees who


Reply
Views: 2709  
Thread Tools Rate Thread
  #1  
Old 05-26-2009, 01:12 PM
bholus7
Guest
 
Posts: n/a
Default Update statements in Query I have one query, Update the salary of the employees who

Update statements in Query

I have one query,
Update the salary of the employees who has finished one year service with the company by 10% and who has service less than 1 yr with the company by only 5%.


Thesre are basically two Update statements in your Query:
1. Update the salary of the employees who has finished one year service with the company by 10%
Ans:
update table employee set salary = (salary + 0.10*salary) where (to_number(to_char(sysdate,'YYYY'))
- to_number(to_char(hiredate,'YYYY'))) > 1
/
2. and who has service less than 1 yr with the company by only 5%
Ans:
Update table employee set salary = (salary + 0.05*salary) where (to_number(to_char(sysdate,'YYYY'))
- to_number(to_char(hiredate,'YYYY'))) < 1
/
I think this is what you needed else revert me back.


Use Decode or Case to solve this problem


Give this inside a loop
{
UPDATE empincrement
SET salincrement =
(
SELECT decode(doj - sysdate >=1 ,sal*.10, sal*.05)
FROM emp
);
}
end loop

This is the way I have tried to solve this, but in the below part
doj - sysdate >=1 , its giving an error like right paranthesis missing.



Use this query in your loop.
update emp
set sal=
(SELECT case when (hiredate - sysdate) >=1 then
sal*.10 else sal*.05 end
FROM emp)

Reply With Quote
Reply

New topics in Oracle Database, SQL, Application, Programming


Thread Tools
Rate This Thread
Rate This Thread:



Powered by vBulletin® Version 3.8.10
Copyright ©2000 - 2022, vBulletin Solutions, Inc.
WikiNewForum)