I have to do a process to perform an update on a given field in a table.
I created a function in mysql. But within this function would need to perform an update, plus the function does not accept uptade would have any way to perform this procedure?
The error that appears is as follows:
Can not update table T043 in stored function / trigger because it is already used by statement which invoked this stored function / trigger
Follow the function I created
CREATE DEFINER='hardness'@'%' FUNCTION 'T059_PAC_Fornecedor_Multiplo'(
xT043_Id int(11),
xT043_Qtd int(11),
xT059_T010_Id int(11)) RETURNS int(11)
BEGIN
DECLARE xPacQtd INT DEFAULT 0;
DECLARE xCount INT DEFAULT 0;
DECLARE xCountResult INT DEFAULT 0;
SET xPacQtd = T059_PAC_Fornecedor(xT043_Id, xT059_T010_Id);
IF ((xT043_Qtd % xPacQtd) = 0) THEN
return xT043_Qtd;
END IF;
sloop:LOOP
SET xCount=xCount+1;
SET xCountResult = (xPacQtd*xCount);
IF xCountResult > xT043_Qtd THEN
UPDATE T043 SET T043_QUANTIDADE = xCountResult WHERE T043_Id = xT043_Id;
LEAVE sloop;
END IF;
END LOOP sloop;
RETURN xCountResult;
END