MySQL-Fehler 1093 - In der FROM-Klausel kann die Zieltabelle für die Aktualisierung nicht angegeben werden

Question

Ich habe eine Tabelle story_category in meiner Datenbank mit fehlerhaften Einträgen. Die nächste Abfrage gibt die beschädigten Einträge zurück:

SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);

Ich habe versucht, sie zu löschen und auszuführen:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id);

Aber ich erhalte den nächsten Fehler:

1093 - Sie können die Zieltabelle 'story_category' für die Aktualisierung in der FROM-Klausel nicht angeben

Wie kann ich dieses Problem lösen?

Answer 1

Aktualisierung: Diese Antwort behandelt die allgemeine Fehlerklassifizierung. Eine spezifischere Antwort darauf, wie die genaue Anfrage des Auftraggebers am besten zu behandeln ist, finden Sie in anderen Antworten auf diese Frage

In MySQL können Sie nicht die gleiche Tabelle ändern, die Sie im SELECT-Teil verwenden.
Dieses Verhalten ist dokumentiert unter: http://dev.mysql.com/doc/refman/5.6/en/update.html

Vielleicht können Sie die Tabelle einfach mit sich selbst verbinden

Wenn die Logik einfach genug ist, um die Abfrage umzugestalten, lassen Sie die Unterabfrage weg und verbinden Sie die Tabelle mit sich selbst, indem Sie geeignete Auswahlkriterien verwenden. Dadurch wird MySQL veranlasst, die Tabelle als zwei verschiedene Dinge zu betrachten, so dass destruktive Änderungen vorgenommen werden können.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Versuchen Sie alternativ, die Unterabfrage tiefer in eine from-Klausel zu schachteln ...

Wenn Sie die Unterabfrage unbedingt benötigen, gibt es eine Umgehungslösung, aber die ist hässlich aus mehreren Gründen, einschließlich Leistung:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

Die verschachtelte Unterabfrage in der FROM-Klausel erzeugt eine implizit temporär Tabelle so dass sie nicht als dieselbe Tabelle gilt, die Sie aktualisieren.

... aber achten Sie auf den Abfrageoptimierer

Beachten Sie jedoch, dass von MySQL 5.7.6 und weiter, kann der Optimierer die Unterabfrage optimieren und Ihnen trotzdem den Fehler anzeigen. Glücklicherweise ist die optimizer_switch kann verwendet werden, um dieses Verhalten abzuschalten, obwohl ich dies nur als kurzfristige Lösung oder für kleine, einmalige Aufgaben empfehlen kann.

SET optimizer_switch = 'derived_merge=off';

Dank an Peter V. Mørch für diesen Ratschlag in den Kommentaren.

Die Beispieltechnik stammt von Baron Schwartz, ursprünglich veröffentlicht bei Nabble , hier paraphrasiert und erweitert.

Answer 2

NexusRex sofern ein sehr gute Lösung zum Löschen mit Join aus derselben Tabelle.

Wenn Sie dies tun:

DELETE FROM story_category
WHERE category_id NOT IN (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
)

werden Sie eine Fehlermeldung erhalten.

Aber wenn Sie die Bedingung in ein weiteres Select einpacken:

DELETE FROM story_category
WHERE category_id NOT IN (
    SELECT cid FROM (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
    ) AS c
)

würde es das Richtige tun!!

Erläuterung: Der Abfrageoptimierer führt eine abgeleitete Merge-Optimierung für die erste Abfrage (wodurch sie mit dem Fehler fehlschlägt), aber die zweite Abfrage qualifiziert sich nicht für die abgeleitete Merge-Optimierung . Daher ist der Optimierer gezwungen, die Unterabfrage zuerst auszuführen.

Answer 3

El inner join in Ihrer Unterabfrage ist unnötig. Es sieht so aus, als wollten Sie die Einträge in story_category wo die category_id ist nicht in der category Tisch.

Stattdessen:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN
         story_category ON category_id=category.id);

Tun Sie dies:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

Answer 4

Kürzlich musste ich Datensätze in derselben Tabelle aktualisieren, die ich wie folgt erstellt habe:

UPDATE skills AS s, (SELECT id  FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development' 
WHERE s.id = p.id;

Answer 5

Wenn Sie das nicht können

UPDATE table SET a=value WHERE x IN
    (SELECT x FROM table WHERE condition);

weil es sich um dieselbe Tabelle handelt, können Sie tricksen und tun:

UPDATE table SET a=value WHERE x IN
    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[aktualisieren oder löschen oder was auch immer]