oracle SQL increase fields value

Question

Hello I have got a simple Question in SQL oracle. Which is I need to increase employee'a salary by 7% if its under IT department, 12% under business department, AND 5% for other departments lastly display revised salary with two decimal places heres my employees table as follow:

employeeID      Dept       Salary
emp1             IT         2000
emp2             BS         3000  
emp3             MK         4000
emp4             EG         5000

NOTE: BS = business dept, MK = markiting dept, EG = engineering dept

Use `CASE WHEN` like [here](http://stackoverflow.com/questions/18760632/update-multiple-rows-using-case-when-oracle) — Maheswaran Ravisankar, Oct 31 '15 at 07:44

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

You could use CASE expression in your UPDATE statement. It is verbose and easy to interpret than DECODE.

For example,

To display:

SELECT employeeID,
       Dept,
       Salary,
       CASE
          WHEN dept = 'IT' THEN
             salary + (7/100) * salary
          WHEN dept = 'BS' THEN
             salary + (12/100) * salary
          ELSE 
             salary + (5/100) * salary    
       END as "New_Salary"
FROM employees;

To update:

UPDATE employees
SET salary=  CASE
                WHEN dept = 'IT' THEN
                   salary + (7/100) * salary
                WHEN dept = 'BS' THEN
                   salary + (12/100) * salary
                ELSE 
                   salary + (5/100) * salary    
              END;

edited May 23 '17 at 12:23

Community

1
1

answered Oct 31 '15 at 07:52

Lalit Kumar B

47,486
13
97
124

thanks but i dont want to update, i want to display the new changes along with current values like: empid,dept,old salary,revised salary. – OT AMD Oct 31 '15 at 09:53
@OTAMD Ok, see the updated answer. Please mark it as answered, would help others too. – Lalit Kumar B Oct 31 '15 at 10:00

score 1 · Answer 2 · answered Oct 31 '15 at 07:54

1

Use decode function:

UPDATE employee
      SET salary = salary*decode (dept,
         'IT', 1.07,
         'BS', 1.12,
         ...,
         1.05)

answered Oct 31 '15 at 07:54

Zbynek Vyskovsky - kvr000

18,186
3
35
43

oracle SQL increase fields value

2 Answers2