mysql1442错误,mysql错误1442的真正原因是什么？

well i have looked for a lot of places on the internet for the cause of the mysql error #1442 which says

Can't update table 'unlucky_table' in stored function/trigger because

it is already used by statement which invoked this stored

function/trigger

some say that this is a bug in mysql or a feature that it doesnt provide.

now i cant understand why this is recursive. i have a case in which i have 2 tables table1 and table2 and i run an sql query as

update table1 set avail = 0 where id in (select id from table2 where duration < now() - interval 2 hour);

now i have an after update trigger on table1 as

CREATE TRIGGER trig_table1 AFTER UPDATE ON table1

FOR EACH ROW begin

if old.avail=1 and new.avail=0 then

delete from table2 where id=new.id;

end if;

now when i execute the update query i get a 1442 error.

whats recursive in this case?

is this error a lack of feature in mysql?

OR

does this have to do with how mysql executes queries?

OR

is there something logically wrong with executing such queries?

解决方案

You cannot refer to a table when updating it.

/* my sql does not support this */

UPDATE tableName WHERE 1 = (SELECT 1 FROM tableName)

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. (Before MySQL 5.0.10, a trigger cannot modify other tables.)