error 1093: MySQL can’t specify target table 'SENTIERO' for update in FROM clause
This is my trigger:
CREATE TRIGGER lunghezza_sentiero_datoderivato_INSERT
AFTER INSERT ON SENTIERO_HA_TAPPA
FOR EACH ROW
BEGIN
UPDATE SENTIERO
SET lunghezza= (SELECT SUM(lunghezza)
FROM TAPPA, SENTIERO as S2, SENTIERO_HA_TAPPA
WHERE NEW.IDsentiero=S2.IDsentiero
and SENTIERO_HA_TAPPA.IDtappa=TAPPA.IDtappa);
WHERE IDsentiero IN (SELECT IDsentiero
FROM TAPPA, SENTIERO, SENTIERO_HA_TAPPA
WHERE SENTIERO_HA_TAPPA.IDsentiero=SENTIERO.IDsentiero
and NEW.IDtappa=SENTIERO_HA_TAPPA.IDtappa);
END$$
I've found this article about this issue, check it on
http://verysimple.com/2011/03/30/mysql-cant-specify-target-table-for-update-in-from-clause/
Is it the only chance i have? Please help me
EDIT:: i've just added the 'WHERE' clause but it signals me "error syntax"... why?
Try this instead:
UPDATE SENTIERO s
INNER JOIN
(
SELECT IDsentiero, TheSum
FROM
(
SELECT S2.IDsentiero, SUM(s2.lunghezza) AS TheSum
FROM SENTIERO AS S2
INNER JOIN SENTIERO_HA_TAPPA AS st ON st.IDsentiero = S2.IDsentiero
INNER JOIN TAPPA AS t ON st.IDtappa = t.IDtappa
GROUP BY S2.IDsentiero
) AS Sub
) AS s2 ON s.IDsentiero = s2.IDsentiero
SET s.lunghezza = s2.TheSum
WHERE S2.IDsentiero = NEW.IDsentiero;
You have to get the sum of the values of lunghezza
from the TAPPA
table not from the SENTIERO
, thats why you were getting NULL
values. So the final CREATE TRIGGER
code should be like this:
CREATE TRIGGER lunghezza_sentiero_datoderivato_INSERT
AFTER INSERT ON SENTIERO_HA_TAPPA
FOR EACH ROW
BEGIN
UPDATE SENTIERO s
INNER JOIN
(
SELECT IDsentiero, TheSum
FROM
(
SELECT S2.IDsentiero, SUM(t.lunghezza) AS TheSum
FROM SENTIERO AS S2
INNER JOIN SENTIERO_HA_TAPPA AS st ON st.IDsentiero = S2.IDsentiero
INNER JOIN TAPPA AS t ON st.IDtappa = t.IDtappa
GROUP BY S2.IDsentiero
) AS Sub
) AS s2 ON s.IDsentiero = s2.IDsentiero
SET s.lunghezza = s2.TheSum;
END
Note that: This trigger will update the values of lunghezza
in the table SENTIERO
, when any row being inserted into the table SENTIERO_HA_TAPPA
for all the IDsentiero
in the SENTIERO
, not just the value of the new inserted IDsentiero
.
To update only the value of lunghezza
for the new inserted value of IDsentiero
into the table SENTIERO_HA_TAPPA
only, add a WHERE S2.IDsentiero = NEW.IDsentiero
to the UPDATE
statement of the trigger. Like this:
CREATE TRIGGER lunghezza_sentiero_datoderivato_INSERT
AFTER INSERT ON SENTIERO_HA_TAPPA
FOR EACH ROW
BEGIN
UPDATE SENTIERO s
INNER JOIN
(
SELECT IDsentiero, TheSum
FROM
(
SELECT S2.IDsentiero, SUM(t.lunghezza) AS TheSum
FROM SENTIERO AS S2
INNER JOIN SENTIERO_HA_TAPPA AS st ON st.IDsentiero = S2.IDsentiero
INNER JOIN TAPPA AS t ON st.IDtappa = t.IDtappa
GROUP BY S2.IDsentiero
) AS Sub
) AS s2 ON s.IDsentiero = s2.IDsentiero
SET s.lunghezza = s2.TheSum
WHERE S2.IDsentiero = NEW.IDsentiero;
END;
For instance, if you create the three tables, then insert the data to the tables and then create that trigger. Then do an insert into the table SENTIERO_HA_TAPPA
like this:
INSERT INTO `SENTIERO_HA_TAPPA` (`IDtappa`, `IDsentiero`) VALUES (14, 4);
Then the trigger will update the value of the IDsentiero = 4
only in the table SENTIERO
, not all the values of it. And the values of lunghezza
for other IDsentiero
's will be NULL
s:
So, you have to create your tables and the triggers before any insert. Then do the insertions into the tables, so that you get a consistent data. That's how it should work.
Like in the following demo:
Note that: In all the demos in this answer, I used only the three tables involved with the trigger, also I modified the two fields inizio
, fine
to be nullable in the table TAPPA
, because your insert clauses into that table have NULL
values to those columns.