UPDATE und DELETE über zwei Tabellen

Die Form, die überall läuft

Die WHERE-Klausel eines UPDATE ist eine gewöhnliche WHERE-Klausel, eine Unterabfrage kann die Zeilen also auswählen. Keine Engine braucht dafür etwas Besonderes.

-- Works on every engine in this list, unchanged.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id FROM orders WHERE status = 'refunded' AND total > 2000
);

Kommt der neue Wert selbst aus der anderen Tabelle, erledigt das eine korrelierte Unterabfrage in SET:

-- The same shape when the new value comes from the other table.
UPDATE products
SET stock_quantity = stock_quantity + (
  SELECT COALESCE(SUM(change_qty), 0)
  FROM inventory_logs il
  WHERE il.product_id = products.id
    AND il.reason = 'return'
)
WHERE id IN (SELECT product_id FROM inventory_logs WHERE reason = 'return');

Beachten Sie die wiederholte Bedingung. Die Unterabfrage in SET bestimmt den Wert, die in WHERE bestimmt, welche Zeilen überhaupt angefasst werden – ohne die zweite würde jedes Produkt aktualisiert, und die ohne passenden Logeintrag würden per COALESCE(..., 0) gesetzt: Es kommt nichts hinzu, geschrieben wird die Zeile trotzdem.

Diese Form ist portabel und gut lesbar. Sie ist jedoch nicht immer die schnellste: Eine Engine kann die korrelierte Unterabfrage für jede Zeile ausführen. Ob das passiert, zeigt der Ausführungsplan. Bei großen Updates kann deshalb eine Join-Syntax trotz geringerer Portabilität sinnvoll sein.

MySQL und MariaDB

-- The join goes between UPDATE and SET.
UPDATE customers c
  JOIN orders o ON o.customer_id = c.id
SET c.loyalty_points = c.loyalty_points + 10
WHERE o.status = 'refunded';

-- DELETE names which table's rows to remove, before FROM.
DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

Der Join steht zwischen UPDATE und SET, anders als bei jeder anderen Engine hier. DELETE verlangt, dass das Ziel vor FROM genannt wird – DELETE c FROM customers c JOIN ... –, weil bei zwei gejointen Tabellen sonst unklar wäre, wessen Zeilen entfernt werden. Diesen Alias wegzulassen ist ein Syntaxfehler, das ist der gute Fall; den falschen zu nennen ist es nicht.

PostgreSQL

-- The second table goes in FROM, and the join condition in WHERE.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded';

-- DELETE uses USING rather than FROM for the extra table.
DELETE FROM wishlists
USING products p
WHERE p.id = wishlists.product_id
  AND p.stock_quantity = 0;

Die zusätzliche Tabelle steht in FROM, und die Join-Bedingung wandert in WHERE. Zwei Details geraten leicht falsch: Die Zieltabelle wird in FROM nicht wiederholt (sonst joinen Sie sie mit sich selbst), und DELETE verwendet USING, wo UPDATE FROM verwendet.

SQLite hat UPDATE … FROM mit PostgreSQLs Semantik in 3.33 (2020) übernommen, deshalb läuft das dritte Beispiel dieser Seite hier:

-- SQLite 3.33 (2020) and later, with PostgreSQL's spelling.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded'
  AND o.total > 2500;

Ein DELETE … USING hat SQLite nicht, Löschungen laufen dort also weiterhin über eine Unterabfrage.

SQL Server

-- The alias is updated, and the FROM clause repeats the target table.
UPDATE c
SET c.loyalty_points = c.loyalty_points + 10
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

Aktualisiert wird der Alias, und FROM wiederholt die Zieltabelle, um diesem Alias eine Definition zu geben. Das liest sich merkwürdig – die Tabelle wird für eine Anweisung zweimal genannt –, aber es ist die einzige Form, die T-SQL akzeptiert, und DELETE folgt demselben Muster.

Oracle

-- Oracle has no UPDATE ... FROM and no UPDATE ... JOIN.
-- EXISTS is the portable answer, and the one to use.
UPDATE customers c
SET c.loyalty_points = c.loyalty_points + 10
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'refunded'
);

DELETE FROM wishlists w
WHERE EXISTS (
  SELECT 1 FROM products p
  WHERE p.id = w.product_id AND p.stock_quantity = 0
);

Oracle hat weder UPDATE … JOIN noch UPDATE … FROM. EXISTS mit einer korrelierten Unterabfrage ist die übliche Antwort, und es ist dieselbe Anweisung, die auf den anderen vier Engines läuft.

Es gibt eine zweite Möglichkeit, die aktualisierbare Join-Sicht – UPDATE (SELECT a.col, b.col FROM a JOIN b ON …) SET … –, aber Oracle akzeptiert sie nur, wenn es beweisen kann, dass der Join die Schlüssel erhält, was eine Unique-Bedingung oder einen Primärschlüssel auf der gejointen Spalte voraussetzt. Kann es das nicht, meldet es ORA-01779, und die Lösung ist die Rückkehr zu EXISTS.

Bevor Sie eine davon ausführen

Bei UPDATE und DELETE mit Join ist die betroffene Menge schwieriger zu erkennen als bei einem einfachen WHERE. Planche kann diese Anweisungen daher vor der Ausführung in ein entsprechendes SELECT umschreiben. Mit Einstellungen › Sicherheit › Betroffene Zeilen vorab anzeigen sehen Sie zunächst Anzahl und Stichprobe. Die Umschreibung unterstützt die fünf oben beschriebenen Formen; ein Skript im Repository vergleicht die abgeleitete Anzahl mit den tatsächlich geänderten Zeilen auf MySQL, PostgreSQL, SQL Server, SQLite und Oracle.