Is it possible to execute 2 queries at the same time in MySQL?

-1

I need to create a stored procedure to update the salary of employees who earn less than 1000 and give a 10% increase. Other employees (who earn over 1000) will have a 15% reduction. I've done the following:

 DELIMITER $

 CREATE PROCEDURE modificaSalario2()

 BEGIN

        update Funcionario set Salario=Salario*1.10 where Salario<1000;

        update Funcionario set Salario=Salario-(Salario*0.15) where Salario>1000;

 END $
 DELIMITER ;

The problem is that it is possible to give the increase of 10% and then give the discount of 15%. Ex: an employee who earns R $ 950 receives a 10% increase and starts receiving R $ 1045. Shortly thereafter he will suffer a 15% reduction and earn $ 888.25.

    
asked by anonymous 03.12.2018 / 02:09

2 answers

3

I do not understand why you need two updates , you can do with just one, using case when :

DELIMITER $
CREATE PROCEDURE modificaSalario2()
BEGIN
    UPDATE Funcionario 
    SET Salario = Salario * (CASE WHEN Salario < 1000 THEN 1.10 ELSE 0.85 END);
END $
DELIMITER ;
    
03.12.2018 / 14:00
-1

For your case, the simplest way is to make a subquery to retrieve all the IDs (Primary Key) for you to update the correct record. See below how it would look:

CREATE PROCEDURE modificaSalario2()

 BEGIN

    UPDATE Funcionario SET Salario=Salario*1.10 where ID in (select ID WHERE Funcionario WHERE Salario <= 1000);

    UPDATE Funcionario SET Salario= Salario - (Salario*0.15) where ID in (select ID WHERE Funcionario WHERE Salario > 1000);

 END $
 DELIMITER ;
    
03.12.2018 / 12:10