| Author |
 Topic  |
|
|
sunsanvin
Flowing Fount of Yak Knowledge
India
1256 Posts |
 Posted - 11/21/2006 : 05:30:38
|
Dear Friends,
I have two tables........emp and salgrade
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.
Vinod |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 11/21/2006 : 05:35:29
|
update e
set e.hike = case
when s.grade = 1 then 0.4
when s.grade = 2 then 0.3
when s.grade = 3 then 0.2
when s.grade = 4 then 0.1
...
from emp e
inner join salgrade s on s.<somecol> = e.<somecol>
Peter Larsson
Helsingborg, Sweden |
 |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/21/2006 : 05:36:41
|
Update e
set col = col + case sg.grade
when 1 then col * 0.4
when 2 then col * 0.3
when 3 then col * 0.2
when 4 then col * 0.1
end
from emp e join salgrade sg
on e.id = sg.id
Edit: oooooooouch !!!
Harsh Athalye
India.
"Nothing is Impossible" |
Edited by - harsh_athalye on 11/21/2006 05:38:01 |
|
|
|
sunsanvin
Flowing Fount of Yak Knowledge
India
1256 Posts |
Posted - 11/21/2006 : 05:39:08
|
Thank you peso and harsha
thank you very much
Vinod |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 11/21/2006 : 05:41:30
|
If you want some read-only code, update e
set e.hike = (5 - s.grade) / 10
from emp e
inner join salgrade s on s.<somecol> = e.<somecol>
where s.grade between 1 and 4
Peter Larsson
Helsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 11/21/2006 : 11:40:30
|
quote:
Originally posted by Peso
If you want some read-only code, update e
set e.hike = (5 - s.grade) / 10
from emp e
inner join salgrade s on s.<somecol> = e.<somecol>
where s.grade between 1 and 4
Peter Larsson
Helsingborg, Sweden
If both are integers wont that give 0 for 3/10, 4/10, etc?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 11/21/2006 : 12:16:48
|
Yes.
Easily fixed by dividing by 10.0
Peter Larsson
Helsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 11/22/2006 : 11:22:21
|
or by (5 - s.grade)*1.0 / 10
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
procedure needed pls help
