Is it possible to run 2 queries at the same time in Mysql? Stored Procedure

Asked

Viewed 501 times

-1

I need to create a stored procedure to update the salaries of employees who earn less than 1000 and give a 10% increase. Other employees (who earn over 1000) will have 15% reduction. I did 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 there is possibility 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 receives R$1045. Soon after it will suffer a reduction of 15% and earn $ 888,25.

  • This case of yours would be best handled with a case when, not with two updates. This looks like a similar case to shoot coconuts with cannons

  • By the way, when you have row of appointments in a Procedure, they are executed sequentially. There are no mechanisms that make two queries "in parallel" in the same batch, be it Procedure or even a visual client that allows writing and executing queries

2 answers

3


I don’t understand why you need two updates, can do with only 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 ;
  • 1

    Thank you so much for your help! I am a student and still did not know the expression case when. Its resolution worked perfectly, I only needed to change the value 0.15 to 0.85.

  • 1

    I changed the answer to the correct value. Preferably, mark the answer as accepted so the question is not open ;) (why accept an answer?)

-1

For your case, the simplest way is to do a subquery to recover all the ID (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 ;
  • This remains in the same problem presented in the question. If, by chance, the employee of ID=1 has the salary of 990 real, the first consultation will transform the salary in 1089 real, hence the second consultation will transform the value to 925,65 real

  • You’re right!!! Is there a field in this table that signals when there was the last update in the record? If yes you could use the following command: Where ID in (select ID WHERE Funcionario WHERE Salario <= 1000 and DATE_SUB(CONCAT(CURDATE(), '00:00:00'), INTERVAL 1 DAY) < dt_update);

  • Otherwise create a stored procedure: CREATE PROCEDURE atualizaSalario() BEGIN declare v_IDFunctioning int; declare v_SalarioFunctioning decimal(10,2);

  • declare cur1 cursor for select id,salario from Funcionario Where is_active; declare continue Handler for not found set done=1; set done = 0; open cur1; aLoop: loop fetch cur1 into v_IDFunctioning,v_SalarioFunctioning; IF v_SalarioFunctioning > 1000 UPDATE Employee SET Salary=v_SalarioFunctioning1.10 Where Where ID = v_IDFunctional; ELSE UPDATE Employee SET Salary= v_SalarioFunctional - (v_SalarioFunctional0.15) Where ID = v_IDFunction; END IF;

  • Or why don’t you do it right all at once? Why don’t you do the mathematically multiplexing?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.