What is the real cause of mysql 1442 error?

Question

Well, I searched a lot of places on the Internet for the reason mysql error #1442 , which says

It is not possible to update the "unlucky_table" table in a stored function / trigger because it is already in use by the statement that called this Function / trigger

Some say this is a bug in mysql or a function that it does not provide.

MySQL triggers cannot control the table to which they are bound. All other major DBMSs support this feature, so hopefully MySQL will add this support soon.

Some argue that this is due to recursive behavior when you insert a record, mysql does some locking. you cannot insert / update / delete rows of the same table you are inserting .. because then the trigger will call again and again .. ends in recursion

During insert / update, you have access to the NEW object, which contains all the fields in the table. If you do before inserting / updating and edit the fields that you want to change in the new object, it will become part of the calling statement and will not be executed separately (excluding recursion)

Now I can’t understand why this is recursive. I have a case where I have 2 tables table1 and table2 , and I run the sql query as

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

now I have 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 request, I get error 1442. What is 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? 

Answer 1

You cannot reference a table when updating it.

 /* my sql does not support this */ UPDATE tableName WHERE 1 = (SELECT 1 FROM tableName) 

From MySQL Docs:

A trigger can access old and new data in its table. A trigger can also affect other tables, but it is not allowed to modify a table that is already in use (for reading or writing) by the operator invoking the function or trigger. (Before MySQL 5.0.10, a trigger cannot modify other tables.)