sql merge - Oracle: come UPSERT (aggiornare o inserire in una tabella?)





insert update (11)


Un'alternativa a MERGE (la "vecchia maniera"):

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;   

L'operazione UPSERT aggiorna o inserisce una riga in una tabella, a seconda che la tabella abbia già una riga che corrisponde ai dati:

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

Poiché Oracle non ha una specifica dichiarazione UPSERT, qual è il modo migliore per farlo?




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

"In Oracle9i, un UPSERT può svolgere questo compito in una singola istruzione:"

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;



Nessuna delle risposte fornite finora è sicura di fronte agli accessi concorrenti , come sottolineato nel commento di Tim Sylvester, e solleverà eccezioni in caso di razze. Per risolvere ciò, la combinazione di inserimento / aggiornamento deve essere racchiusa in una sorta di istruzione di loop, in modo che in caso di un'eccezione l'intera operazione venga ritentata.

Ad esempio, ecco come il codice di Grommit può essere avvolto in un ciclo per renderlo sicuro quando eseguito contemporaneamente:

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; 

NB In modalità di transazione SERIALIZABLE , che non consiglio btw, è possibile eseguire ORA-08177: non è possibile serializzare l'accesso per queste eccezioni di transazione .




L' istruzione MERGE unisce i dati tra due tabelle. L'uso di DUAL ci consente di utilizzare questo comando. Si noti che questo non è protetto contro l'accesso concorrente.

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



Copia e incolla l'esempio per far avanzare una tabella in un'altra, con 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

Risultato:

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



  1. inserire se non esiste
  2. aggiornare:
    
INSERT INTO mytable (id1, t1) 
  SELECT 11, 'x1' FROM DUAL 
  WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11); 

UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;



Un'altra alternativa senza controllo di eccezione:

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;



Il doppio esempio sopra il quale si trova in PL / SQL era ottimo perché volevo fare qualcosa di simile, ma volevo che fosse client side ... quindi ecco l'SQL che usavo per inviare una dichiarazione simile diretta da qualche 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" )

Tuttavia, dal punto di vista del C #, questo è più lento di fare l'aggiornamento e vedere se le righe interessate erano 0 e fare l'inserto se lo era.




Una nota riguardante le due soluzioni che suggeriscono:

1) Inserisci, se l'eccezione poi aggiorna,

o

2) Aggiorna, se sql% rowcount = 0 quindi inserisci

La domanda se inserire o aggiornare prima dipende anche dall'applicazione. Ti aspetti più inserti o più aggiornamenti? Quello che ha maggiori probabilità di successo dovrebbe andare per primo.

Se scegli quella sbagliata otterrai un sacco di letture non necessarie dell'indice. Non è un grosso problema ma c'è ancora qualcosa da considerare.




Sto usando il primo campione di codice per anni. Notare non contato piuttosto che contare.

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;

Il codice seguente è il codice eventualmente nuovo e migliorato

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)

Nel primo esempio l'aggiornamento esegue una ricerca dell'indice. Deve, per aggiornare la riga giusta. Oracle apre un cursore implicito e lo usiamo per avvolgere un inserto corrispondente in modo che sappiamo che l'inserimento avverrà solo quando la chiave non esiste. Ma l'inserto è un comando indipendente e deve fare una seconda ricerca. Non conosco il funzionamento interno del comando di unione, ma poiché il comando è una singola unità, Oracle potrebbe eseguire l'inserimento o l'aggiornamento corretto con una singola ricerca di indice.

Penso che l'unione sia migliore quando si ha a che fare con l'elaborazione che significa prendere i dati da alcune tabelle e aggiornare una tabella, possibilmente inserendo o eliminando le righe. Ma per il caso a riga singola, si può considerare il primo caso poiché la sintassi è più comune.




L'esempio seguente utilizza una tabella derivata, un'istruzione SELECT dopo la clausola FROM, per restituire i valori vecchi e nuovi per ulteriori aggiornamenti:

UPDATE x
SET    x.col1 = x.newCol1,
       x.col2 = x.newCol2
FROM   (SELECT t.col1,
               t2.col1 AS newCol1,
               t.col2,
               t2.col2 AS newCol2
        FROM   [table] t
               JOIN other_table t2
                 ON t.ID = t2.ID) x




sql oracle merge upsert