sql - tipos - subconsultas oracle

Usar una subconsulta en lugar de un nombre de tabla en una declaración de actualización de Oracle

Question

Necesito escribir una declaración de actualización que use varias tablas para determinar qué filas actualizar, ya que en Oracle no se permiten varias tablas. La siguiente consulta arrojará un error "ORA-00971: Missing SET keyword"

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

Al buscar la sintaxis de la sentencia UPDATE en Oracle, encontré el siguiente enlace , que muestra que puede usar una subconsulta en lugar de un nombre de tabla.

Cuando traté de escribir la consulta de esta manera, obtuve un "ORA-01779: no se puede modificar una columna que se asigna a una tabla sin clave conservada"

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''

Reescribí la consulta (se muestra a continuación) utilizando una instrucción EXISTS y funciona bien, pero me gustaría saber cómo se hace.

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
  )

¡Gracias! -Nate

Answer 1

Cada fila en el conjunto de resultados de la consulta en su cláusula UPDATE debe correlacionarse con una y solo una fila de la tabla que está tratando de actualizar, y de forma que Oracle pueda seguirla automáticamente. Dado que la consulta es realmente una vista, una forma de pensar es que Oracle necesita poder unir la vista a la tabla de destino, para saber qué fila actualizar.

Esto significa esencialmente que debe incluir la clave principal de la tabla de destino en esa consulta. Es posible que también pueda usar otros campos de índice únicos, pero no puedo garantizar que Oracle DBMS sea lo suficientemente inteligente como para permitir eso.

Answer 2

Cuando realiza una actualización, obviamente solo puede decirle al sistema que actualice el valor a un único valor nuevo, diciéndole que actualice "X" a "Y" y "Z" no tiene sentido. Por lo tanto, cuando base una actualización en el resultado de una vista en línea, Oracle verifica que existen suficientes restricciones para evitar que una columna modificada se actualice dos veces.

En su caso, espero que TABLE2.PK no sea realmente una clave primaria declarada. Si coloca una restricción primaria o única en esa columna, sería bueno que se vaya.

Hay una pista no documentada para evitar la verificación de cardinalidad de la combinación de actualización, utilizada internamente por Oracle, pero no aconsejaría su uso.

Una solución para esto es usar una instrucción MERGE, que no está sujeta a la misma prueba.

Answer 3

La sintaxis de su ejemplo está bien, pero Oracle requiere que la subconsulta incluya claves principales. Esa es una limitación bastante significativa.

En una nota relacionada, también puede usar paréntesis para usar 2 o más campos en una declaración IN, como en:

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

Otra opción:

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

Su segundo ejemplo funcionaría si (a) la vista incluyera el PK declarado de 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''

... y (b) TABLE1.FK era una clave externa declarada para TABLE2

(Por declarado quiero decir que existe una restricción y está habilitada).

Answer 5

Encuentro que una forma agradable, rápida y consistente de convertir una instrucción SELECT en una ACTUALIZACIÓN es hacer que la actualización se base en el ROWID.

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'')
    )

Entonces, su consulta interna está definiendo las filas para actualizar.

Answer 6

Encontré lo que necesitaba aquí: Comandos útiles de SQL

Necesitaba actualizar una tabla con el resultado de una unión
Probé las soluciones anteriores sin éxito :(

Aquí hay un extracto de la página que señalé arriba
Usando cursores pude lograr la tarea con éxito
Estoy seguro de que hay otra solución, pero esta funcionó así que ...

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;

Nota: no te olvides de cometer ;-)