Search code examples
sqloracleoracle-sqldeveloper

Is it possible to write two UPDATE functions with two conditions in a single query?

What I want to achieve is a single query to update those two values. If possible.

I have a table named employee , with 3 columns that are salary ,city and employees

I already completed what was needed to be done,

What I needed to do was :

  • City 1 : update the current salary of +15% of the employees that make <800 a month.
  • City 2 : update the current salary of +18% of the employees that make <800 a month.

city salary employees
city1 650 employee1
cit2 700 employee2

my two separate query are:

#1

UPDATE employee
SET salary=  salary+ (salary* 15 / 100)
WHERE city= 'city1' AND salary<=800;

#2

UPDATE employee
SET salary=  salary+ (salary* 18 / 100)
WHERE city= 'city2' AND salary<=800;;
Solution

  • You can use a case expression:

    UPDATE employee
    SET salary = salary * (1 + CASE WHEN city = 'city1' THEN 15 ELSE 18 END) / 100
    WHERE city IN ('city1', 'city2') AND salary <= 800;