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.

mysql sql stored-procedures

  • 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 mysql sql stored-procedures

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