Oracle: jak UPSERT (zaktualizować lub wstawić do tabeli?)

Operacja UPSERT aktualizuje lub wstawia wiersz do tabeli, w zależności od tego, czy tabela ma już wiersz zgodny z danymi:

if table t has a row exists that has key X:
    update t set mystuff... where mykey=X
else
    insert into t mystuff...

Ponieważ Oracle nie ma określonej instrukcji UPSERT, jaki jest najlepszy sposób na to?

Alternatywa dla MERGE („staromodny sposób”):

begin
   insert into t (mykey, mystuff) 
      values ('X', 123);
exception
   when dup_val_on_index then
      update t 
      set    mystuff = 123 
      where  mykey = 'X';
end;   

Instrukcja MERGE scala dane między dwiema tabelami. Użycie DUAL pozwala nam użyć tego polecenia. Pamiętaj, że nie jest to chronione przed równoczesnym dostępem.

create or replace
procedure ups(xa number)
as
begin
    merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;

A                      B
---------------------- ----------------------
10                     2
20                     1

Podwójny przykład powyżej, który jest w PL / SQL, był świetny, ponieważ chciałem zrobić coś podobnego, ale chciałem, aby to było po stronie klienta ... więc tutaj jest SQL, który wysłałem podobną instrukcję bezpośrednio z jakiegoś C #

MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name") 
    VALUES ( 2097153,"smith", "john" )

Jednak z perspektywy C # zapewnia to wolniejsze działanie niż przeprowadzanie aktualizacji i sprawdzanie, czy zmienione wiersze miały wartość 0, i wykonywanie wstawiania, jeśli tak było.

Inna alternatywa bez kontroli wyjątku:

UPDATE tablename
    SET val1 = in_val1,
        val2 = in_val2
    WHERE val3 = in_val3;

IF ( sql%rowcount = 0 )
    THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

1. wstaw, jeśli nie istnieje
2. aktualizacja:

    
INSERT INTO mytable (id1, t1) 
  WYBIERZ 11, „x1” OD DUAL 
  GDZIE NIE ISTNIEJE (WYBIERZ id1 Z mytble GDZIE id1 = 11); 

AKTUALIZACJA mytable SET t1 = „x1” GDZIE id1 = 11;

Żadna z dotychczas udzielonych odpowiedzi nie jest bezpieczna w obliczu równoczesnego dostępu , jak wskazano w komentarzu Tima Sylvester'a, i nie podniesie wyjątków w przypadku wyścigów. Aby to naprawić, zestaw wstawiania / aktualizacji musi być zawinięty w jakąś instrukcję pętli, aby w przypadku wyjątku ponowna próba została wykonana.

Oto przykład, w jaki sposób kod Grommita można owinąć w pętlę, aby był bezpieczny, gdy jest uruchamiany jednocześnie:

PROCEDURE MyProc (
 ...
) IS
BEGIN
 LOOP
  BEGIN
    MERGE INTO Employee USING dual ON ( "id"=2097153 )
      WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
      WHEN NOT MATCHED THEN INSERT ("id","last","name") 
        VALUES ( 2097153,"smith", "john" );
    EXIT; -- success? -> exit loop
  EXCEPTION
    WHEN NO_DATA_FOUND THEN -- the entry was concurrently deleted
      NULL; -- exception? -> no op, i.e. continue looping
    WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted
      NULL; -- exception? -> no op, i.e. continue looping
  END;
 END LOOP;
END; 

Uwaga: W trybie transakcji SERIALIZABLE, którego nie polecam btw, możesz wpaść na ORA-08177: zamiast tego nie możesz serializować dostępu dla wyjątków transakcji .

Chciałbym uzyskać odpowiedź Grommita, z wyjątkiem tego, że wymaga wartości duplikowania. Znalazłem rozwiązanie, w którym może się pojawić raz: http://forums.devshed.com/showpost.php?p=1182653&postcount=2

MERGE INTO KBS.NUFUS_MUHTARLIK B
USING (
    SELECT '028-01' CILT, '25' SAYFA, '6' KUTUK, '46603404838' MERNIS_NO
    FROM DUAL
) E
ON (B.MERNIS_NO = E.MERNIS_NO)
WHEN MATCHED THEN
    UPDATE SET B.CILT = E.CILT, B.SAYFA = E.SAYFA, B.KUTUK = E.KUTUK
WHEN NOT MATCHED THEN
    INSERT (  CILT,   SAYFA,   KUTUK,   MERNIS_NO)
    VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO); 

Uwaga dotycząca dwóch sugerowanych rozwiązań:

1) Wstaw, jeśli wyjątek, a następnie zaktualizuj,

lub

2) Zaktualizuj, jeśli sql% rowcount = 0, następnie wstaw

Pytanie, czy najpierw wstawić, czy zaktualizować, zależy również od aplikacji. Oczekujesz więcej wstawek lub aktualizacji? Ten, który najprawdopodobniej odniesie sukces, powinien zająć pierwsze miejsce.

Jeśli wybierzesz niewłaściwy, otrzymasz niepotrzebne odczyty indeksu. Nie jest to wielka sprawa, ale wciąż coś do rozważenia.

Pierwszej próbki kodu używam od lat. Zauważ, że nie znaleziono, a nie policz.

UPDATE tablename SET val1 = in_val1, val2 = in_val2
    WHERE val3 = in_val3;
IF ( sql%notfound ) THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

Poniższy kod jest prawdopodobnie nowym i ulepszonym kodem

MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN INSERT 
    VALUES (in_val1, in_val2, in_val3)

W pierwszym przykładzie aktualizacja wykonuje wyszukiwanie indeksu. Musi to zrobić, aby zaktualizować prawy wiersz. Oracle otwiera niejawny kursor i używamy go do zawijania odpowiedniej wstawki, abyśmy wiedzieli, że wstawka nastąpi tylko wtedy, gdy klucz nie istnieje. Ale wstawka jest niezależnym poleceniem i musi wykonać drugie wyszukiwanie. Nie znam wewnętrznego działania polecenia scalania, ale ponieważ polecenie to pojedyncza jednostka, Oracle może wykonać poprawne wstawienie lub aktualizację za pomocą pojedynczego wyszukiwania indeksu.

Myślę, że scalanie jest lepsze, gdy trzeba wykonać pewne przetwarzanie, co oznacza pobieranie danych z niektórych tabel i aktualizowanie tabeli, ewentualnie wstawianie lub usuwanie wierszy. Ale w przypadku pojedynczego wiersza można rozważyć pierwszy przypadek, ponieważ składnia jest częstsza.

Skopiuj i wklej przykład wstawiania jednej tabeli do drugiej za pomocą MERGE:

CREATE GLOBAL TEMPORARY TABLE t1
    (id VARCHAR2(5) ,
     value VARCHAR2(5),
     value2 VARCHAR2(5)
     )
  ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE t2
    (id VARCHAR2(5) ,
     value VARCHAR2(5),
     value2 VARCHAR2(5))
  ON COMMIT DELETE ROWS;
ALTER TABLE t2 ADD CONSTRAINT PK_LKP_MIGRATION_INFO PRIMARY KEY (id);

insert into t1 values ('a','1','1');
insert into t1 values ('b','4','5');
insert into t2 values ('b','2','2');
insert into t2 values ('c','3','3');


merge into t2
using t1
on (t1.id = t2.id) 
when matched then 
  update set t2.value = t1.value,
  t2.value2 = t1.value2
when not matched then
  insert (t2.id, t2.value, t2.value2)  
  values(t1.id, t1.value, t1.value2);

select * from t2

Wynik:

1. b 4 5
2. c 3 3
3. a 1 1

Spróbuj tego,

insert into b_building_property (
  select
    'AREA_IN_COMMON_USE_DOUBLE','Area in Common Use','DOUBLE', null, 9000, 9
  from dual
)
minus
(
  select * from b_building_property where id = 9
)
;

Od http://www.praetoriate.com/oracle_tips_upserts.htm :

„W Oracle9i UPSERT może wykonać to zadanie za pomocą jednego polecenia:”

INSERT
FIRST WHEN
   credit_limit >=100000
THEN INTO
   rich_customers
VALUES(cust_id,cust_credit_limit)
   INTO customers
ELSE
   INTO customers SELECT * FROM new_customers;