Verwendung einer Subquery anstelle eines Tabellennamens in einer Oracle Update-Anweisung

Question

Ich muss eine Aktualisierungsanweisung schreiben, die mehrere Tabellen verwendet, um zu bestimmen, welche Zeilen aktualisiert werden sollen, da in Oracle mehrere Tabellen nicht zulässig sind. Die folgende Abfrage gibt einen "ORA-00971: Fehlendes SET-Schlüsselwort"-Fehler

UPDATE
  TABLE1 a,
  TABLE2 b
SET
  a.COL1 = 'VALUE'
WHERE
  a.FK = b.PK
  AND b.COL2 IN ('SET OF VALUES')

Beim Nachschlagen der Syntax der UPDATE-Anweisung in Oracle habe ich Folgendes gefunden enlace die zeigt, dass Sie eine Unterabfrage anstelle eines Tabellennamens verwenden können.

Als ich versuchte, die Abfrage so zu schreiben, erhielt ich die Meldung "ORA-01779: Cannot modify a column which maps to a non key-preserved table"

UPDATE
  (
    SELECT
      a.COL1
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
  ) update_tbl
SET
  update_tbl.COL1 = 'VALUE'

Ich habe die Abfrage umgeschrieben (siehe unten), indem ich stattdessen eine EXISTS-Anweisung verwendet habe, und es funktioniert gut, aber ich würde trotzdem gerne wissen, wie das gemacht wird.

UPDATE
  TABLE1 update_tbl
SET
  update_tbl.COL1 = 'VALUE'
WHERE
  EXISTS (
    SELECT
      1
    FROM
      TABLE1 a
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
      AND update_tbl.PK = a.PK
  )

Danke! -Nate

Answer 1

Eine andere Möglichkeit:

UPDATE TABLE1 a
SET a.COL1 = 'VALUE'
WHERE a.FK IN
( SELECT b.PK FROM TABLE2 b
  WHERE b.COL2 IN ('SET OF VALUES')
)

Ihr zweites Beispiel würde funktionieren, wenn (a) die Ansicht die erklärt PK von TABLE1:

UPDATE
  (
    SELECT
      a.COL1, a.PKCOL
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
  ) update_tbl
SET
  update_tbl.COL1 = 'VALUE'

... und (b) TABLE1.FK war ein erklärt Fremdschlüssel zu TABLE2

(Mit "erklärt" meine ich, dass eine Einschränkung existiert und aktiviert ist).

Answer 2

Eine gute, schnelle und konsistente Möglichkeit, eine SELECT-Anweisung in eine UPDATE-Anweisung zu verwandeln, besteht darin, die Aktualisierung auf der Grundlage der ROWID vorzunehmen.

UPDATE
  TABLE1
SET
  COL1 = 'VALUE'
WHERE
  ROWID in
    (
    SELECT
      a.rowid
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
    )

Ihre innere Abfrage definiert also die zu aktualisierenden Zeilen.

Answer 3

Die Syntax Ihres Beispiels ist in Ordnung, aber Oracle verlangt, dass die Unterabfrage Primärschlüssel enthält. Das ist eine ziemlich große Einschränkung.

In diesem Zusammenhang können Sie auch Klammern verwenden, um 2 oder mehr Felder in einer IN-Anweisung zu verwenden, wie in:

UPDATE
  TABLE1 update_tbl
SET
  update_tbl.COL1 = 'VALUE'
WHERE
  (update_tbl.PK1, update_tbl.pk2) in(
                      select some_field1, some_field2
                      from some_table st
                      where st.some_fields = 'some conditions'
                      );

Answer 4

Wenn Sie eine Aktualisierung durchführen, können Sie dem System natürlich nur sagen, dass es den Wert auf einen einzigen neuen Wert aktualisieren soll - ihm zu sagen, dass es "X" sowohl auf "Y" als auch auf "Z" aktualisieren soll, macht keinen Sinn. Wenn Sie also eine Aktualisierung auf der Grundlage des Ergebnisses einer Inline-Ansicht vornehmen, prüft Oracle, ob genügend Einschränkungen vorhanden sind, um zu verhindern, dass eine geänderte Spalte möglicherweise zweimal aktualisiert wird.

In Ihrem Fall gehe ich davon aus, dass TABLE2.PK nicht wirklich ein deklarierter Primärschlüssel ist. Wenn Sie eine primäre oder eindeutige Einschränkung auf dieser Spalte platzieren, dann wäre alles in Ordnung.

Es gibt einen undokumentierten Hinweis zur Umgehung der Update-Join-Kardinalitätsprüfung, der intern von Oracle verwendet wird, aber ich würde nicht empfehlen, ihn zu verwenden.

Eine Umgehung dieses Problems ist die Verwendung einer MERGE-Anweisung, die nicht demselben Test unterliegt.

Answer 5

Ich habe hier gefunden, was ich brauchte: Nützliche SQL-Befehle

Ich musste eine Tabelle mit dem Ergebnis einer Verknüpfung aktualisieren
Ich habe die oben genannten Lösungen ohne Erfolg ausprobiert :(

Hier ist ein Auszug aus der Seite, auf die ich oben verwiesen habe
Mit Hilfe von Cursorn konnte ich die Aufgabe erfolgreich lösen
Ich bin sicher, dass es eine andere Lösung gibt, aber diese hat funktioniert...

DECLARE

 /* Output variables to hold the result of the query: */
 a T1.e%TYPE;
 b T2.f%TYPE;
 c T2.g%TYPE;

 /* Cursor declaration: */
 CURSOR T1Cursor IS
   SELECT T1.e, T2.f, T2.g
   FROM T1, T2
   WHERE T1.id = T2.id AND T1.e <> T2.f

 FOR UPDATE;

BEGIN

  OPEN T1Cursor;

  LOOP

    /* Retrieve each row of the result of the above query
    into PL/SQL variables: */
    FETCH T1Cursor INTO a, b;

    /* If there are no more rows to fetch, exit the loop: */
    EXIT WHEN T1Cursor%NOTFOUND;

    /* Delete the current tuple: */
    DELETE FROM T1 WHERE CURRENT OF T1Cursor;

    /* Insert the reverse tuple: */
    INSERT INTO T1 VALUES(b, a);

    /* Here is my stuff using the variables to update my table */
    UPDATE T2
    SET T2.f = a
    WHERE T2.id = c;

  END LOOP;

  /* Free cursor used by the query. */
  CLOSE T1Cursor;

END;
.
run;

Hinweis: Vergessen Sie nicht, sich zu verpflichten ;-)