6

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.

MySQL triggers can't manipulate the table they are assigned to. All other major DBMS support this feature so hopefully MySQL will add this support soon.

Some claim that this is due to recursive behavior when you insert a record mysql is doing some lock stuff. you can't insert/update/delete rows of the same table where you insert.. because then the trigger would called again and again.. ending up in a recursion

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

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?
Community
  • 1
  • 1
lovesh
  • 5,235
  • 9
  • 62
  • 93

1 Answers1

4

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.)
SiHa
  • 7,830
  • 13
  • 34
  • 43
BradLaney
  • 2,384
  • 1
  • 19
  • 28
  • its UPDATE tableName WHERE id in (SELECT id FROM tableName) – lovesh Jul 20 '11 at 02:52
  • I was just generally commenting that you cannot select from a table you are updating. It doesn't matter what is in the where as long as its the same tablename. I'm looking up why that trigger fails right now. But I'm 99% sure the above is why it does not work. – BradLaney Jul 20 '11 at 02:53
  • I just edited my comment with text directly from MySQL website. That is why it does not work. It is not supported. – BradLaney Jul 20 '11 at 02:56
  • so why lot of people say its because of recursive behavior ? did u see the link in my ques? there are lot of links at that site that say the same? is it just a misconception? – lovesh Jul 20 '11 at 03:00
  • 1
    My first thought is why does it matter? From as long as I remember they do not support selecting from a table being used in an update. Big issue if you have to write queries like that, which is why we have other options. But MySQL has advantages of simple queries being faster. This is probably because they don't support it. Maybe they will in the future. There is absolutely no reason they can't build it in. – BradLaney Jul 20 '11 at 03:06
  • 1
    You can actually update the same table that is being updated by putting your UPDATE statement in a function and calling it in your trigger. That way, you'll be able to update the table without error 1442. – Adam Fowler Jul 03 '13 at 19:49
  • @AdamF I tried this in SQLFiddle, you can't do this even in either [function](http://sqlfiddle.com/#!2/5e9a6c) or [procedure](http://sqlfiddle.com/#!2/9beaad) called from a trigger. – ADTC Dec 03 '13 at 09:10