Update table in mysql using stored procedure

2
votes

I have stored procedure in mysql. The procedure is created, but when the procedure is called I get an error:

"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 -> Query Editor and reconnect."

Here is the procedure:

------------------------------------------------
drop procedure if exists update_per_det;

delimiter //

create procedure update_per_det(IN name varchar(30))

begin

 DECLARE age1 int;

set age1=(select CalAge(name));

update  per_det set age=age1 where username=name;

end;//

delimiter ;

How can I solve this issue?

2
mysql
please learn how to format quotes, code, etc. Read the FAQ for more information. - JonH

2 Answers

2
votes

Try this:

set age1=(select CalAge(name));

SET SQL_SAFE_UPDATES=0;

update  per_det set age=age1 where username=name;
0
votes

Use DELIMITER like this:

DROP PROCEDURE IF EXISTS DealStateChange;

DELIMITER //

CREATE PROCEDURE DealStateChange(statusID tinyint)



     UPDATE deals
          SET `status` = statusID
      WHERE `id` = 1001;




//
DELIMITER ;


/* for execute PROCEDURE */
CALL DealStateChange(11);