ORA-38104: Столбцы, указанные в разделе ON, не могут быть обновлены

У меня есть простая таблица с флагом удаления (записи должны быть обновлены в этом столбце, а не удалены):

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

При вставке новых записей я должен проверить, есть ли уже запись, соответствующая первичному ключу, но имеющая ISDELETED = 1. В этом случае я должен изменить ISDELETED на 0 и обновить другие столбцы. Поэтому я использую следующий Merge-Statement:

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

На Sql-сервере он отлично работает, но Oracle говорит:

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

Если есть сопоставимая запись с IDELETED = 0, я хочу, чтобы исключение первичного ключа являлось исключением, поэтому я не могу переместить "TARGET.ISDELETED = 1" из предложения on в оператор update.

Ответы

Ответ 1

Я подозреваю, что вам лучше в этом случае с алгоритмом "побег".

В зависимости от того, что вы ожидаете от более частых случаев:

  • Обновить, и если строки не обновлены, вставьте; или
  • Вставить и, если есть нарушение ключа, обновить.

Ответ 2

В отличие от принятого ответа, на самом деле есть способ отключить это: переместите бит-бит из предложения ON и в предложение WHERE оператора обновления:

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

Ответ 3

Нам также нужно рассмотреть ниже сценарий,

Если есть соответствующая запись с IDELETED = 0, я хочу, чтобы исключение первичного ключа являлось исключением, поэтому я не могу переместить "TARGET.ISDELETED = 1" из предложения on в оператор update.

Итак, точное решение, как показано ниже,

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;