asked on MySQL: Error 1442 in a SELECT
Hi
I have the following code to insert all missing rows (only rows that are actually not in the table):
INSERT INTO myDetailTable(Field1, Field2)
SELECT A.Field1, C.FieldX FROM myMasterTable A
INNER JOIN myThirdTable C
LEFT JOIN myDetailTable E ON E.Id = C.Id AND E.aField = A.aField
WHERE E.Id IS NULL
Here I get the error 1442 (an't update table 'myDetailTable ' in stored function/trigger because it is already used by statement which invoked this stored function/trigger). I have with other tables the same select and there it works. I don't see the reasion. Thanks, Peter
I have the following code to insert all missing rows (only rows that are actually not in the table):
INSERT INTO myDetailTable(Field1, Field2)
SELECT A.Field1, C.FieldX FROM myMasterTable A
INNER JOIN myThirdTable C
LEFT JOIN myDetailTable E ON E.Id = C.Id AND E.aField = A.aField
WHERE E.Id IS NULL
Here I get the error 1442 (an't update table 'myDetailTable ' in stored function/trigger because it is already used by statement which invoked this stored function/trigger). I have with other tables the same select and there it works. I don't see the reasion. Thanks, Peter
MySQL Server
Last Comment
formi
ASKER
Hi Mohit Anand
but how can I solve it? (as I sayed above with another table it works ...). There must be a difference. Here is the code of those that works (sql version 5.7.20):
but how can I solve it? (as I sayed above with another table it works ...). There must be a difference. Here is the code of those that works (sql version 5.7.20):
INSERT INTO insMandantenDetail (IdMandant, MandantenNr,TabellenGruppe) SELECT A.Id, A.Id, B.TabellenGruppe FROM insMandanten AS A
INNER JOIN (SELECT TabellenGruppe FROM allTabellen GROUP BY Tabellengruppe) AS B
LEFT JOIN insMandantenDetail C ON C.IdMandant = A.Id AND C.TabellenGruppe = B.TabellenGruppe
WHERE C.Id IS NULL
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You cannot refer to a table when updating it.
/* my sql does not support this */