well i have looked for a lot of places on the internet for the cause of the
mysql error #1442
Can't update table 'unlucky_table' in stored function/trigger because
it is already used by statement which invoked this stored
update table1 set avail = 0 where id in (select id from table2 where duration < now() - interval 2 hour);
after update trigger
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;
is this error a lack of feature in mysql?
does this have to do with how mysql executes queries?
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)
From MySQL Docs:
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.)