pasar - transponer columnas en filas sql

ORA-38104: las columnas a las que se hace referencia en la cláusula ON no se pueden actualizar

Question

Tengo una tabla simple con un indicador de eliminación (los registros deben actualizarse en esta columna en lugar de eliminarse):

create table PSEUDODELETETABLE
(
  ID        NUMBER(8) not null, -- PKEY
  NAME      VARCHAR2(50) not null,
  ISDELETED NUMBER(1) default 0 not null
)

Al insertar nuevos registros, debo verificar si ya existe un registro que coincida con la clave principal pero que tenga el valor ISDELETED = 1. En ese caso, debo cambiar el valor de ISDELETED a 0 y actualizar las otras columnas. Por lo tanto estoy usando la siguiente declaración de fusión:

merge into ET.PSEUDODELETETABLE TARGET
using (select 1 as ID, ''Horst'' as NAME from sys.dual) SOURCE
on (TARGET.ISDELETED = 1 and SOURCE.ID = TARGET.ID)
when matched then
  update set ISDELETED = 0, NAME = SOURCE.NAME
when not matched then
  insert values (SOURCE.ID, SOURCE.NAME, 0);

En Sql-Server funciona muy bien, pero Oracle dice:

ORA-38104: Columns referenced in the ON Clause cannot be updated: TARGET.ISDELETED

Si hay un registro coincidente con IDELETED = 0, quiero que la violación de la clave principal sea una excepción, es por eso que no puedo mover "TARGET.ISDELETED = 1" de la cláusula on a la instrucción update.

Answer 1

"Las inserciones y actualizaciones condicionales ahora son posibles mediante el uso de una cláusula WHERE en estas declaraciones". http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php

Answer 2

Contrariamente a la respuesta aceptada, en realidad hay una manera de lograrlo: mueva el bit ofensivo de la cláusula ON y colóquela en la cláusula WHERE de la instrucción de actualización:

merge into ET.PSEUDODELETETABLE TARGET
using (select 1 as ID, ''Horst'' as NAME from sys.dual) SOURCE
on (SOURCE.ID = TARGET.ID)
when matched then
  update 
      set ISDELETED = 0, 
      NAME = SOURCE.NAME
  where TARGET.ISDELETED = 1 -- Magic!
when not matched then
  insert 
      values (SOURCE.ID, SOURCE.NAME, 0);

Answer 3

Sospecho que estás mejor en este caso con un algoritmo de disparar y mirar.

Dependiendo de lo que esperas que sea el caso más frecuente, ya sea:

• Actualice, y si no se actualizan filas, inserte; o
• Insertar, y si hay una violación clave, actualizar.

Answer 4

También debemos considerar el siguiente escenario,

Si hay un registro coincidente con IDELETED = 0 , quiero que la violación de la clave principal sea una excepción, es por eso que no puedo mover "TARGET.ISDELETED = 1" de la cláusula on a la instrucción update.

Así que la solución exacta es la siguiente,

begin 
    update ET.PSEUDODELETETABLE set ISDELETED = 0, NAME = ''Horst'' 
    where ISDELETED = 1 and ID = 1; 
    if (sql%rowcount = 0) then 
        insert into ET.PSEUDODELETETABLE values (1, ''Horst'', 0); 
    end if; 
end;