MySQL Error 1442

Question

I have two tables:

sanctionwith these attributes: DriverID, Calification, Points
peoplewith these attributes: pID, city,TotalPoints

And I have this trigger:

DROP TRIGGER IF EXISTS updatePoints_tgr;

delimiter $$
CREATE  TRIGGER
updatePoints_tgr AFTER UPDATE
ON sanctions FOR EACH ROW
 BEGIN
     if NEW.points > OLD.points 
     THEN
         UPDATE people
         SET TotalPoints = TotalPoints + (NEW.points - OLD.points)
         WHERE people.pID = NEW.DriverID;
     elseif NEW.points < OLD.points 
     THEN
         UPDATE people
         SET TotalPoints = TotalPoints - (NEW.points - OLD.points)
         WHERE people.pID = NEW.DriverID;
     END IF;
 END$$
 delimiter ;

And when I try to perform this update

UPDATE sanctions 
JOIN people ON sanctions.DriverID=people.pID
SET points=points+6
WHERE city='Barcelona'  AND Calification='LightPenalty'

I get this error:

It is not possible to update the people table in a stored function / trigger because it is already in use by the operator that called this stored function / trigger.

How can i fix this? Thank.

I am using Windows 10 server and MySQL 5.7.17

Answer 1

You can not update the table ( sanctionsand people), which is caused by a trigger:

, ( ) , .

1442:

Error Code: 1442
Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

---

. JOIN sanctions people UPDATE - , , ' " ".

---

- UPDATE JOIN ( people) - , , , .