MySQL 错误 1442 : Can't Update Table, 但未引用表？

Question

我正在编写一个程序来遍历各种昆虫收集器，其中一些以逗号分隔，例如。 “V. Shirey, L. Smith”，我需要从中为每个收藏家创建一个新行。我的程序如下所示:

DROP PROCEDURE IF EXISTS agent_reclamation;
DELIMITER //
CREATE PROCEDURE agent_reclamation (IN VerbatimName VARCHAR(170)) 
BEGIN
DECLARE verbatimNameHandler varchar(170);
DECLARE tempAgentName varchar(170); 
SET verbatimNameHandler = VerbatimName;
    WHILE LENGTH(verbatimNameHandler) > 0 DO -- while there's more stuff left
        IF LOCATE(',', verbatimNameHandler) > 0 THEN -- and theres a comma to be found
            SET tempAgentName = SUBSTRING(verbatimNameHandler,1,LOCATE(',',verbatimNameHandler) - 1); -- set the temp variable to everything from the first character to the first comma
        ELSE
            SET tempAgentName = verbatimNameHandler; -- set the name if there are no commas
            SET verbatimNameHandler = ''; -- won't accept procedure without update --
        END IF;
            INSERT INTO agentReclamation SET tempAgentName = tempAgentName;
        /* INSERT INTO agentReclamation(tempAgentName) VALUES (tempAgentName); */ -- insert the new names into the agentReclamation table
        SET verbatimNameHandler = REPLACE(verbatimNameHandler, tempAgentName + ',', ''); -- won't accept procedure without update --
    END WHILE;
END //

DELIMITER ;

SELECT agent_reclamation(VerbatimName) FROM tempAgent WHERE VerbatimName LIKE 
'%,%';

我收到错误 1442，指出无法更新存储函数/触发器中的表“tempAgent”，因为它用于调用此存储函数/触发器的语句中。我看不到我将自己定位在哪里以使用该过程在任何地方更新“tempAgent”。

如有任何帮助，我将不胜感激，我目前刚刚掌握了编写自己的程序的窍门。谢谢!

Answer 1

我所做的是创建两个过程，其中一个过程通过对原始过程的过程调用迭代每一行。见下文:

DROP PROCEDURE IF EXISTS agent_reclamation;
DROP PROCEDURE IF EXISTS procIteration;
DELIMITER //

CREATE PROCEDURE procIteration ()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE verbatimNameHandler varchar(170);
DECLARE cur CURSOR FOR SELECT VerbatimName FROM tempAgent WHERE VerbatimName LIKE '%,%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

OPEN cur;

testLoop: LOOP
    FETCH cur INTO verbatimNameHandler;
    IF done THEN 
        LEAVE testLoop;
    END IF;
    CALL agent_reclamation(verbatimNameHandler);
END LOOP testLoop;

CLOSE cur;
END //

CREATE PROCEDURE agent_reclamation (IN VerbatimName VARCHAR(170)) 
BEGIN
DECLARE verbatimNameHandler varchar(170);
DECLARE tempAgentName varchar(170); 
SET verbatimNameHandler = VerbatimName;
    WHILE LENGTH(verbatimNameHandler) > 0 DO -- while there's more stuff left
        IF LOCATE(',', verbatimNameHandler) > 0 THEN -- and theres a comma to be found
            SET tempAgentName = SUBSTRING(verbatimNameHandler,1,LOCATE(',',verbatimNameHandler) - 1); -- set the temp variable to everything from the first character to the first comma
        ELSE
            SET tempAgentName = verbatimNameHandler; -- set the name if there are no commas
            SET verbatimNameHandler = ''; -- won't accept procedure without update --
        END IF;
        -- INSERT INTO agentReclamation SET tempAgentName = tempAgentName;
        INSERT INTO agentReclamation(tempAgentName) VALUES (tempAgentName);  -- insert the new names into the agentReclamation table
        SET verbatimNameHandler = REPLACE(verbatimNameHandler, CONCAT(tempAgentName, ','), ''); -- won't accept procedure without update --
    END WHILE;
END //

DELIMITER ;

CALL procIteration();

这会产生预期的结果。