MySQL Error 1442 -

i have 2 tables:

sanction these attributes: driverid, calification, points
people these attributes: pid, city , totalpoints

and have trigger:

drop trigger if exists updatepoints_tgr;  delimiter $$ create  trigger updatepoints_tgr after update on sanctions each row  begin      if new.points > old.points                update people          set totalpoints = totalpoints + (new.points - old.points)          people.pid = new.driverid;      elseif new.points < old.points                update people          set totalpoints = totalpoints - (new.points - old.points)          people.pid = new.driverid;      end if;  end$$  delimiter ; 

and when try execute update

update sanctions  join people on sanctions.driverid=people.pid set points=points+6 city='barcelona'  , calification='lightpenalty' 

i error:

can't update table 'people' in stored function/trigger because used statement invoked stored function/trigger.

how can fix it? thanks.

i use windows 10 , mysql server 5.7.17

you cannot update table (sanctions , people) trigger invoked:

within stored function or trigger, not permitted modify table being used (for reading or writing) statement invoked function or trigger.

doing generate error 1442:

error code: 1442 can't update table 'mytable' in stored function/trigger because used statement invoked stored function/trigger. 

---

your case: confusion can due join on sanctions , people update - making both table in use, stated above 'used' means 'reading or writing'.

---

to test - try update query without join (with people) - if works, surely case.