Safe update error using stored Procedure in MYSQL

Question

I am having trouble updating my stock table using a stored Procedure in MYSQL. Follows the code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `atualiza_estoque`(id_produto int)
BEGIN

update estoque e inner join reposicao r on r.produto = e.produto 
set e.qtd = if (e.qtd = 0, r.qtd, e.qtd+r.qtd), e.data_entrada = now()
where e.produto = id_produto and r.produto=id_produto and r.data_reposicao > e.data_entrada;
END

When calling the trial call atualiza_estoque(1);, error message is displayed

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and Reconnect.

The funny thing is that I was able to run the trial twice before this message appeared. What was causing this error? I tried to pass the stock id as parameter, but the same message appears.

Note: I know it is possible to disable safe update, but I would like to understand what is causing this error, since Procedure worked perfectly twice before presenting the error.

Answer 1

The safa update error comes from the need to have a WHERE that uses the KEY column in the PRIMARY KEY case of the table to be changed. This comes as standard from the Workbench, you can see more here So make sure you’re using the right column on the filter.

The quick solution to the error is to disable safe update or apply this code before running the update:

SET SQL_SAFE_UPDATES=0;

I have also made a change in your consultation, which has remained so:

CREATE DEFINER=`root`@`localhost` PROCEDURE `atualiza_estoque`(id_produto int)
BEGIN

    update estoque e 
inner join reposicao r 
        on r.produto = e.produto 
       and r.data_reposicao > e.data_entrada
       set e.qtd = if (e.qtd = 0, r.qtd, e.qtd + r.qtd), e.data_entrada = now()
     where e.produto = id_produto   
END

You can test to see if everything is working.