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.)