Safe update error using stored procedure in MYSQL

Question

Safe update error using stored procedure in MYSQL

I'm having trouble updating my stock table using a stored procedure in MYSQL. Follow 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 procedure call atualiza_estoque(1);, the error message

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, I managed to run the procedure twice before this message appears. What would be causing this error? I tried passing the stock id as a 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 the procedure worked perfectly twice before presenting the error.

1

mysql sql stored-procedures banco-de-dados

Author: Ryan Santos, 2017-05-12

Source

1 answers

score 1 · Answer 1

The error of safa update 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 it is to ensure that you are using the right column in the filter.

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

SET SQL_SAFE_UPDATES=0;

I also made a change to your query, which looked like this:

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 for see if everything is working.