CODEWITHSUNDEEP
sqloracle-databaseoracle11g
OT AMD
OT AMD

Reputation: 183

oracle SQL increase fields value

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

Upvotes: 0

Views: 122

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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;

Upvotes: 1

Zbynek Vyskovsky - kvr000
Zbynek Vyskovsky - kvr000

Reputation: 18825

Use decode function:

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

Upvotes: 1

Related Questions