El concepto de vistas actualizables se apoya el el concepto de tablas reservadas (protegidas) por clave (‘key-preserved tables’). Este concepto de Vistas actualizables es uno de los primeros con los que se topan los que vienen de entornos SQLServer/Sybase, donde se permite la construcción UPDATE … FROM TABLA1, TABLA2…
Así es: en SQLServer/Sybase se pueden incorporar ‘joins’ de diferentes tablas a la hora de efectuar un UPDATE. En Oracle, en cambio, eso se hace mediante las mencionadas ‘vistas actualizables’.
Lo que en SQLServer/Sybase se escribe como
UPDATE
FROM PRUEBA01 a,
PRUEBA02 b
SET b.C_TXT = a.C_TXT
WHERE a.ID_N = b.ID_N
go
en Oracle se escribe (entre otras formas) como:
UPDATE ( SELECT a.ID_N a_ID_N,
a.C_TXT a_C_TXT,
b.ID_N b_ID_N,
b.C_TXT b_C_TXT
FROM PRUEBA01 a,
PRUEBA02 b
WHERE a.ID_N = b.ID_N)
SET b_C_TXT = a_C_TXT;
¿Mejor? ¿Peor? Como siempre: simplemente distinto. El problema aparece cuando estas sentencias empiezan a arrojar los ‘ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave’ (‘ORA-01779: cannot modify a column which maps to a non key-preserved table’). Es aquí donde la mayoría de la gente se comienza a liar con esto de las ‘key-preserved tables’.
Lo primero que hay que decir es que el concepto de ‘key-preserved tables’ no tiene que ver con los datos, sino con la estructura de los objetos. No importa que la relación entre las filas de dos tablas sea ‘de facto’ 1:1, si el optimizador (que es quien decide) interpreta que las tablas no son ‘key-preserved’ porque su estructura no lo indica, saltará el error.
Veámoslo con un ejemplo:
SQL*Plus: Release 10.2.0.1.0 - Production on Jue Oct 25 14:31:19 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Introduzca el nombre de usuario: carlos@db01.xxxxxx Introduzca la contraseña: Conectado a: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options carlos@db01.xxxxxx> CREATE TABLE PRUEBA01(ID_N NUMBER NOT NULL,
2 C_TXT VARCHAR2(25)); Tabla creada. carlos@db01.xxxxxx> CREATE TABLE PRUEBA02(ID_N NUMBER NOT NULL,
2 C_TXT VARCHAR2(25));
Tabla creada.
carlos@db01.xxxxxx> INSERT INTO PRUEBA01 VALUES (1,'UNO');
1 fila creada.
carlos@db01.xxxxxx> INSERT INTO PRUEBA02 VALUES (1,NULL);
1 fila creada.
carlos@db01.xxxxxx> COMMIT;
Confirmación terminada.
carlos@db01.xxxxxx> SELECT * FROM PRUEBA01;
ID_N C_TXT
---------- -------------------------
1 UNO
carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;
ID_N C_TXT
---------- -------------------------
1
carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N,
2 a.C_TXT a_C_TXT,
3 b.ID_N b_ID_N,
4 b.C_TXT b_C_TXT
5 FROM PRUEBA01 a,
6 PRUEBA02 b
7 WHERE a.ID_N = b.ID_N)
8 SET b_C_TXT = a_C_TXT;
SET b_C_TXT = a_C_TXT
*
ERROR en línea 8:
ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave
Lo segundo que hay que entender es que una tabla es ‘key-preserved’ si todas las claves de dicha tabla podrían ser claves del resultado del ‘join’. Esto, dicho así, puede no parecer tan fácil de entender, pero la traducción a ‘román paladino’ sería que una fila de una tabla no puede ser actualizada más de una vez como resultado de la condición del ‘join’ (esto es: que coincida con más de una fila de la segunda tabla).
¿Y dónde mira el optimizador? A la estructura de la tabla (las PRIMARY KEYS):
carlos@db01.xxxxxx> ALTER TABLE PRUEBA01
2 ADD CONSTRAINT PRUEBA01_PK PRIMARY KEY(ID_N); Tabla modificada. carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N, 2 a.C_TXT a_C_TXT, 3 b.ID_N b_ID_N, 4 b.C_TXT b_C_TXT 5 FROM PRUEBA01 a, 6 PRUEBA02 b 7 WHERE a.ID_N = b.ID_N) 8 SET b_C_TXT = a_C_TXT; 1 fila actualizada.
Y, por último: el mensaje de error es un poco confuso ya que:
carlos@db01.xxxxxx> ALTER TABLE PRUEBA01 DROP CONSTRAINT PRUEBA01_PK; Tabla modificada. carlos@db01.xxxxxx> ALTER TABLE PRUEBA02
2 ADD CONSTRAINT PRUEBA02_PK PRIMARY KEY(ID_N);
Tabla modificada.
carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N,
2 a.C_TXT a_C_TXT,
3 b.ID_N b_ID_N,
4 b.C_TXT b_C_TXT
5 FROM PRUEBA01 a,
6 PRUEBA02 b
7 WHERE a.ID_N = b.ID_N)
8 SET b_C_TXT = a_C_TXT;
SET b_C_TXT = a_C_TXT
*
ERROR en línea 8:
ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave
La columna que estamos modificando pertenece a una tabla que SÍ tiene PK, pero NO ES ‘key-preserved’ (ya que si hubiese dos filas en PRUEBA01 con ID_N=1 provocaría que la misma fila en PRUEBA02 fuese actualizada con valores de más de una fila de PRUEBA01).
Pero hay que tener en cuenta que una fila de una ‘key-preserved table’ SÍ PUEDE actualizar más de una fila de la otra tabla:
carlos@db01.xxxxxx> ALTER TABLE PRUEBA02 DROP CONSTRAINT PRUEBA02_PK; Tabla modificada. carlos@db01.xxxxxx> ALTER TABLE PRUEBA01
2 ADD CONSTRAINT PRUEBA01_PK PRIMARY KEY(ID_N);
Tabla modificada.
carlos@db01.xxxxxx> INSERT INTO PRUEBA02 VALUES (1,'ONE');
1 fila creada.
carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;
ID_N C_TXT
---------- -------------------------
1 UNO
1 ONE
carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N,
2 a.C_TXT a_C_TXT,
3 b.ID_N b_ID_N,
4 b.C_TXT b_C_TXT
5 FROM PRUEBA01 a,
6 PRUEBA02 b
7 WHERE a.ID_N = b.ID_N)
8 SET b_C_TXT = a_C_TXT;
2 filas actualizadas.
carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;
ID_N C_TXT
---------- -------------------------
1 UNO
1 UNO
Nota: El ‘hint’ BYPASS_UJVC evita toda la comprobación por parte del optimizador de las ‘key-preserved tables’, pero es un ‘hint’ indocumentado y -en teoría- los mortales no deberíamos utilizarlo, o hacerlo ‘bajo nuestra propia responsabilidad’…
carlos@db01.xxxxxx> SELECT * FROM PRUEBA01;
ID_N C_TXT
---------- -------------------------
1 UNO
carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;
ID_N C_TXT
---------- -------------------------
1
carlos@db01.xxxxxx> ALTER TABLE PRUEBA01 DROP CONSTRAINT PRUEBA01_PK;
Tabla modificada.
carlos@db01.xxxxxx> ALTER TABLE PRUEBA02 DROP CONSTRAINT PRUEBA02_PK;
Tabla modificada.
carlos@db01.xxxxxx> UPDATE /*+ BYPASS_UJVC */ ( SELECT a.ID_N a_ID_N,
2 a.C_TXT a_C_TXT,
3 b.ID_N b_ID_N,
4 b.C_TXT b_C_TXT
5 FROM PRUEBA01 a,
6 PRUEBA02 b
7 WHERE a.ID_N = b.ID_N)
8 SET b_C_TXT = a_C_TXT;
1 fila actualizada.
Saludos.
Carlos.
CarlosAL 
Muy interesante artículo…
Realmente interesante. Llevaba tres días intentando entender eso que explicas tan bien.
Sólo discrepo en tu respuesta a la pregunta ¿Mejor, peor?
Sí. Es peor. No se por qué Oracle siempre elige la forma más enrevesada de hacer las cosas
[…] verdadera causa del error está en que, como ocurría aquí, dos filas de la tabla PRUEBA02 actualizarían una misma fila de la tabla […]
Gracias por este artículo, de mucha ayuda!!!
saludos
Muchas gracias, me ha sido muy util
Muchisimas gracias, me fue de mucha ayuda; envío mi implementación personal por si a alguien le sirve:
Observaciones:
– la tabla CG (tabla origen) -alias new- tiene una llave primaria en el campo CEGE,
– la tabla servicios_norte2 (tabla destino) -alias old- tiene un campo n_cg que es del mismo tipo que CG.cg
– la tabla servicios_norte2 -alias old- tiene algunos registros con valores en null en el campo n_cg por lo que solo se actualizaran los renglones donde haya una correspondencia entre new.CEGE = old.n_cg
Aqui los queries y una pequeña explicacion:
— Se llena el campo n_cg de la tabla que vamos a actualizar con valores correctos ( que pueden estar relacionados en la otra tabla )
update servicios_norte2 set n_cg = to_number ( cg )
where isnumeric ( cg ) = 1;
UPDATE
(
SELECT
new.cege as new_cege,
old.n_cg,
new.centro as new_centro,
old.centro,
new.desc_centro as new_desc_centro,
old.desc_centro,
new.descrip_cg as new_descrip_cg,
old.descrip_cg,
new.rama as new_rama,
old.rama
FROM
SERVICIOS_NORTE2 old INNER JOIN
CG new
ON new.CEGE = old.n_cg
)
SET
CENTRO = NEW_CENTRO,
DESC_CENTRO = NEW_DESC_CENTRO,
DESCRIP_CG = NEW_DESCRIP_CG,
RAMA = NEW_RAMA;
ESPERO LES SIRVA,
SALUDOS DESDE MEXICO.
[…] Como se ve, primero se hace un ‘JOIN’ entre PRUEBA01 y PRUEBA03 (las que importan) pero, al haber introducido PRUEBA02 en el ‘JOIN’, se produce un ‘CROSS JOIN’ de las dos filas del ‘JOIN’ de PRUEBA01 y PRUEBA03 con todas las filas de PRUEBA02 .Habrá exactamente 10 filas: las dos del primer ‘JOIN’ por las cinco de PRUEBA02 y serán iguales dos a dos, por eso la parte final (MERGE Update) falla con el error “Failure 7547 Target row updated by multiple source rows” (¿Dónde hemos visto esto antes?). […]
Gracias. muy buen aporte
Muchas gracias por la info. Junto a este artículo (https://carlosal.wordpress.com/2007/10/30/merge-y-ora-30926/) también de su autoría pude sacarme todas las dudas respecto a los Update «complicados» en ORACLE.
Gracias por el articulo!