Home » SQL & PL/SQL » SQL & PL/SQL » Update problem (merged)
Update problem (merged) [message #204549] Tue, 21 November 2006 03:56 Go to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

Dear Friends,

I need a procedure to update emp table with the given % hikes.The grades we should take from salgrade table

if the grade =1, then the hike will be 40%
if the grade =2, then the hike will be 30%
if the grade =3, then the hike will be 20%
if the grade =4, then the hike will be 10%


so whenever i execute this procedure, the table should be automatically updated.



thank you verymuch.


[Updated on: Tue, 21 November 2006 04:03]

Report message to a moderator

what is the mistake in this query? [message #204560 is a reply to message #204549] Tue, 21 November 2006 04:52 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

Update emp e
set sal = sal + case sg.grade
when 1 then sal * 0.4
when 2 then sal * 0.3
when 3 then sal * 0.2
when 4 then sal * 0.1
end
from emp e join salgrade sg
on e.id = sg.id

Re: what is the mistake in this query? [message #204582 is a reply to message #204560] Tue, 21 November 2006 05:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use either of the ones.

Update emp e
set sal = sal + (select case when sg.grade = 1 then sal * 0.4
when sg.grade = 2 then sal * 0.3
when sg.grade = 3 then sal * 0.2
when sg.grade = 4 then sal * 0.1
end
from salgrade sg
where e.id = sg.id);

update (select e.sal,sg.id, 
(case when sg.grade = 1 then sal * 0.4
when sg.grade = 2 then sal * 0.3
when sg.grade = 3 then sal * 0.2
when sg.grade = 4 then sal * 0.1
end) new_sal
          from emp e, salgrade sg
         where e.id = sg.id) p
set p.sal = p.sal + p.new_sal;


By
Vamsi
Re: what is the mistake in this query? [message #204595 is a reply to message #204582] Tue, 21 November 2006 06:19 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

Dear Vamsi,
the both tables are emp and salgrade tables of oracle.general tables.there si no id in the salgrade table.


can i place grade in place of ID?


thank you for your response.
Re: what is the mistake in this query? [message #204598 is a reply to message #204595] Tue, 21 November 2006 06:23 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I copied the column names from your question
Quote:

Update emp e
set sal = sal + case sg.grade
when 1 then sal * 0.4
when 2 then sal * 0.3
when 3 then sal * 0.2
when 4 then sal * 0.1
end
from emp e join salgrade sg
on e.id = sg.id



Please change the column names, as they are in the tables.

By
Vamsi.
Previous Topic: Invalid username in sqlplus but can "connect" with same details
Next Topic: Order By Issue....
Goto Forum:
  


Current Time: Wed Dec 07 04:53:29 CST 2016

Total time taken to generate the page: 0.11972 seconds