ERROR 1093 (HY000): You can't specify target table ' ' for update in FROM clause

ERROR 1093 (HY000): You can't specify target table ' ' for update in FROM clause

This error is generated due to wrong logic used in trigger,
that we can not use the same table in trigger, which is being used in external insert / update query.

To overcome, this error, use any external function or in-built function to set the value to filed in trigger.

similar post : -

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is error can be arose while creating trigger in mysql, the error cause of trying to set field value dynamically
while insert/update on same table, which is being used in trigger.

Trigger type - before/after insert

i use below step to replicate the error:

1. create table and function to generate the value for field "tree_hierarchy"

create table hierarchy ( id int , parent_id int , name varchar(20), tree_hierarchy varchar(50));
insert into hierarchy values (1, null, 'parent', 1);
insert into hierarchy values (2, 1, 'lock type', '1_2');
insert into hierarchy values (3, 2, 'personnel lock', '1_2_3');

delimiter //

CREATE FUNCTION hierarchy_sys_connect_by_path(delimiter TEXT, node INT) RETURNS TEXT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE _path TEXT;
    DECLARE _cpath TEXT;
    DECLARE _id INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
    SET _path = _id;
    LOOP
        SELECT  IFNULL(parent_id,0)
        INTO    _id
        FROM    hierarchy
        WHERE   id = _id
                AND COALESCE(id <> @start_with, TRUE);
        SET _path = CONCAT(_id, delimiter, _path);
  END LOOP;
END
//

source -

2. create trigger, the value for field "tree_hierarchy" is set dynamically.
delimiter //

CREATE TRIGGER trigr
after INSERT ON hierarchy
FOR EACH ROW
BEGIN
        update hierarchy set tree_hierarchy =
            concat((SELECT tree_hierarchy FROM hierarchy WHERE id = NEW.parent_id),"_" , NEW.id)
        where id =NEW.id;
END
//

insert into hierarchy (id,parent_id,name) values(4, 3,'af')//

---you might got this error

ERROR 1093 (HY000): You can't specify target table ' ' for update in FROM clause 


3. another type of error can be arose by below trigger

CREATE TRIGGER trigr
after INSERT ON hierarchy
FOR EACH ROW
BEGIN
        update hierarchy set tree_hierarchy = hierarchy_sys_connect_by_path('_',NEW.parent_id) where id =NEW.id;
END
//

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

4. how to resolve the error

DROP TRIGGER IF EXISTS trigr//
CREATE TRIGGER trigr
before INSERT ON hierarchy 
FOR EACH ROW BEGIN 
	SET NEW.tree_hierarchy = CONCAT((SELECT tree_hierarchy FROM hierarchy WHERE id = NEW.parent_id),"_" , NEW.id); 
END 
// 
delimiter ; 

insert into hierarchy (id,parent_id,name) values(4, 3,'af');
result:- no error table has been successfully populated.