您无法在FROM子句中指定目标表“ my_table”进行更新 [英] You can't specify target table 'my_table' for update in FROM clause

问题描述

我正在尝试创建一个SQL触发器，该触发器在更新表后将执行一段SQl以再次对其进行更新

I am trying to create a SQL trigger which after updating the table will execute a piece of SQl to update it again

我有5个可以更新的字段，每个都包含0或1：

I have 5 fields that can be updated that each contain 0 or 1:

step1_complete，step2_complete，step3_complete，step4_complete 和 step5_complete

这些列中的任何一个更新后，我想运行一个触发器来更新 percent_complete 与以下查询相同的表：

after any of these columns are updated I want to run a trigger that will update percent_complete within the same table with the following query:

SELECT sum( step1_complete + 
            step2_complete + 
            step3_complete + 
            step4_complete + 
            step5_complete ) * 20 AS Sum 
FROM completed_part 
GROUP BY id

此查询将返回20、40、60、80或100，这是我期望的，但是当我尝试运行更新查询以查看它是否起作用时，我会收到此错误消息

this query will return either 20, 40, 60, 80 or 100 which is what i expect but when i try to run the update query to see if it works i get this error message

＃1093-您无法在
FROM子句中指定要更新的目标表'completed_pa​​rt'

#1093 - You can't specify target table 'completed_part' for update in FROM clause

我也想知道这是否是创建触发器的正确​​语法

I would also like to know if this is the correct syntax for creating a trigger

DELIMITER $$

CREATE
    TRIGGER `completed_part_after_update` AFTER UPDATE 
    ON `completed_part` 
    FOR EACH ROW BEGIN

        UPDATE completed_part 
        SET percent_complete = (
            SELECT sum( step1_complete +
                        step2_complete +
                        step3_complete +
                        step4_complete + 
                        step5_complete ) * 20 AS Sum
        FROM completed_part 
        GROUP BY id
        );

    END$$

DELIMITER ;

推荐答案

尝试使用更新前 >

BEGIN

SET NEW.percent_complete = ( NEW.step1_complete + NEW.step2_complete + NEW.step3_complete + NEW.step4_complete + NEW.step5_complete ) * 20;

END

这篇关于您无法在FROM子句中指定目标表“ my_table”进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！