Safe update error using stored procedure in MYSQL

Question

Safe update error using stored procedure in MYSQL

Navigation

#1 by (0 votes)

0

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

Error Code: 1175. You are using safe update mode and you tried to update a table without 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 procedure twice before this message appeared. What was causing this error? I tried to pass the stock id as a parameter, but the same message appears.

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

mysql sql stored-procedures

asked by anonymous 13.05.2017 / 00:13

1 answer

Remove data from one table from the average of notes in another table? Error 1452 Mysql

score 0 · 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 standard with the Workbench, you can see more here > So make sure you're using the right column in the filter.

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

SET SQL_SAFE_UPDATES=0;

I also made a change to your query, which looks 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 to see if everything is working.