I have a relatively simple update query that works fine when I manually execute it from a Mysql Workbench query window, but will not work when I call it as a stored procedure. The stored procedure in question:
CREATE PROCEDURE `sp_UpdateGoal`( IN UpdateGoalUserName varchar(45), IN nGoalDate DATE, IN nAmount DECIMAL(9,2), IN nActiveGoal tinyint, IN nOriginalAmount DECIMAL(9,2), IN nChangeLog VarChar(100) ) BEGIN UPDATE salesgoals SET Amount=nAmount, OriginalAmount=nOriginalAmount, `active`=nActiveGoal, ChangeLog=concat(ChangeLog,"\r\n",now()," ",nChangeLog) WHERE salesgoals.username=UpdateGoalUserName AND salesgoals.goalDate=nGoalDate; END
The call to the stored procedure I'm using:
CALL sp_UpdateGoal( 'lauren', "2020-01-01",150000,1,150000,"Testing sp_UpdateGoal");
which returns 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.
I would prefer not to disable safemode.
The manual query that works:
UPDATE salesgoals SET Amount=150000, OriginalAmount=150000, `active`=1, ChangeLog=concat(ChangeLog,"\r\n",now(),'Testing manual query') WHERE username='lauren' AND goalDate='2020-01-01';
SalesGoals is a simple table:
CREATE TABLE `salesgoals` ( `username` varchar(45) NOT NULL, `GoalDate` date NOT NULL, `Amount` decimal(9,2) NOT NULL, `ChangeLog` varchar(500) NOT NULL, `Active` tinyint DEFAULT '1', `OriginalAmount` decimal(9,2) DEFAULT NULL, PRIMARY KEY (`username`,`GoalDate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
It looks to me like when I call the stored procedure, it's ignoring my WHERE clause. I've been staring at this for a crazy long time and don't see anything wrong. What am I missing?