Goldy Agarwal Goldy Agarwal - 6 days ago 5
MySQL Question

Cases in SQL Query?

I have a Query In MySQL

Give all managers of First Bank Corporation a 10 percent salary raise unless the salary become greater than $100000; in such cases, give only a 3 percent raise.


In my Database I have following Table on which i have to work on


  • Employee(emp_name, street, city)

  • Works(emp_name, company_name, salary)

  • Company(company_name, city)

  • Manages(emp_name, manager_name)`



I have done half part of the query, now I have no idea how to do its other part

Update Works
set Salary= salary + salary * 0.10
where company_name = 'First Bank Corporation' AND
emp_name IN (Select manager_name from Manages) AND
salary > 100000;

Answer
UPDATE ...
   SET salary = CASE
                  WHEN salary * 1.10 > 100000
                  THEN salary * 1.03
                  ELSE salary * 1.10
                END
 WHERE ... 

-or-

UPDATE ...
   SET salary = salary * CASE
                           WHEN salary * 1.10 > 100000
                           THEN 1.03
                           ELSE 1.10
                         END
 WHERE ... 
Comments