Home » SQL & PL/SQL » SQL & PL/SQL » changing data with FOR UPDATE..WHERE CURRENT OF (merged 7)
changing data with FOR UPDATE..WHERE CURRENT OF (merged 7) [message #426440] Thu, 15 October 2009 08:40 Go to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Hi,

I've got an assignment and have a question about it:

-------This is the assignment----------------------------
In table employees:
-switch office.nr. 10 and 30
-give employees with a salary off <= 2000 a raise off 8%
-give employees with a salary between 2000 and 2500 a raise off 6%
-give employees with a salary between 2500 and 3500 a raise off 4%
-give employees > 3500 a raise off 2%
-because the company is going internationaly, change the salary
from euro's to dollars (1 euro = 0,95 dollar)

Use FOR UPDATE ....WHERE CURRENT OF
-------End off assignment---------------------------------

I've got the switch off office and changing the currency working (see below) but I can't get the raise in my script working.

And.....how do I let the system give the raises first and then the changing off the currency.

Thanks in advantage,

declare
cursor c_employees is
select *
from employees
for update of officenr;

begin
for r_employees in c_employees loop

update employees
set sal = sal*0.95
where current of c_employees;

if r_employees.officenr = 10 then
update employees
set officenr = 30
where current of c_employees;
elsif r_employees.officenr = 30 then
update employees
set officenr = 10
where current of c_employees;
end if;

end loop;
end;
/
Re: changing data with FOR UPDATE..WHERE CURRENT OF [message #426452 is a reply to message #426440] Thu, 15 October 2009 08:51 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off, when posting code can you please use code tags rather than italics, it's easier to read - see the orafaq forum guide if you're not sure how.

For the raise you can use a case statement:
UPDATE employees
SET salary = CASE WHEN salary <= 2000 THEN salary * 1.08
                  WHEN salary BETWEEN 2000 and 2500 THEN salary * 1.06
                  ....
                  ELSE salary * 1.02
             END
WHERE current of

There is an ambiguity in your problem description. Does someone with a salary of 2500 get a raise of 6% or 4%?

You can use the case idea for office number as well rather than doing seperate updates.

As for the currency change - do that update last of course.
Re: changing data with FOR UPDATE..WHERE CURRENT OF (merged 7) [message #426455 is a reply to message #426440] Thu, 15 October 2009 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think spamming the forum with the same question will give more answers.
Instead take time to read OraFAQ Forum Guide.

Regards
Michel
Re: changing data with FOR UPDATE..WHERE CURRENT OF (merged 7) [message #426460 is a reply to message #426440] Thu, 15 October 2009 09:14 Go to previous messageGo to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Sorry for the merged 7


I got an error message everytime I posted the tread....so I tried a couple off times.


I hope a moderator can delete 6 off the messages?


Thanks
Re: changing data with FOR UPDATE..WHERE CURRENT OF (merged 7) [message #426470 is a reply to message #426440] Thu, 15 October 2009 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course this is an exercise otherwise you can do it in a single update statement.

Regards
Michel

[Updated on: Thu, 15 October 2009 10:15]

Report message to a moderator

Re: changing data with FOR UPDATE..WHERE CURRENT OF (merged 7) [message #427394 is a reply to message #426440] Thu, 22 October 2009 06:30 Go to previous message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Thanks,

Your right Cookiemonster about that ambiguity.

I tried your answer and it works great....thanks man
Previous Topic: Primary and Foreign key relationship
Next Topic: Find Nth row inside an UPDATE
Goto Forum:
  


Current Time: Fri Dec 09 13:50:51 CST 2016

Total time taken to generate the page: 0.10086 seconds