sql - tiempo - Oracle: cómo UPSERT(¿actualizar o insertar en una tabla?)

oracle insert if not exists sql

Question

La operación UPSERT actualiza o inserta una fila en una tabla, dependiendo de si la tabla ya tiene una fila que coincida con los datos:

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

Dado que Oracle no tiene una declaración UPSERT específica, ¿cuál es la mejor manera de hacer esto?

Answer 1

1. insertar si no existe
2. actualizar:

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;

Answer 2

Copie y pegue el ejemplo para cambiar una tabla a otra, 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

Resultado:

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

Answer 3

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

"En Oracle9i, un UPSERT puede realizar esta tarea en una sola declaración:"

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;

Answer 4

El ejemplo doble anterior que se encuentra en PL / SQL fue excelente porque quería hacer algo similar, pero lo quería del lado del cliente ... así que aquí está el SQL que solía enviar una declaración similar directamente desde algún 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" )

Sin embargo, desde una perspectiva de C #, esto permite ser más lento que hacer la actualización y ver si las filas afectadas eran 0 y hacer la inserción si lo fuera.

Answer 5

He estado usando el primer ejemplo de código durante años. Observe que no se encuentra en lugar de contar.

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;

El siguiente código es el código posiblemente nuevo y mejorado.

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)

En el primer ejemplo la actualización hace una búsqueda de índice. Tiene que hacerlo, para actualizar la fila derecha. Oracle abre un cursor implícito, y lo usamos para envolver una inserción correspondiente para que sepamos que la inserción solo sucederá cuando la clave no exista. Pero la inserción es un comando independiente y tiene que hacer una segunda búsqueda. No sé el funcionamiento interno del comando de combinación pero como el comando es una sola unidad, Oracle podría haber ejecutado la inserción o actualización correcta con una única búsqueda de índice.

Creo que la combinación es mejor cuando tienes que realizar un procesamiento que significa tomar datos de algunas tablas y actualizar una tabla, posiblemente insertando o eliminando filas. Pero para el caso de una sola fila, puede considerar el primer caso, ya que la sintaxis es más común.

Answer 6

La instrucción MERGE combina datos entre dos tablas. Usar DUAL nos permite usar este comando. Tenga en cuenta que esto no está protegido contra el acceso simultáneo.

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

Answer 7

Me gustaría la respuesta de Grommit, salvo que requiera valores duplicados. Encontré la solución donde puede aparecer una vez: 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); 

Answer 8

Ninguna de las respuestas proporcionadas hasta ahora es segura frente a accesos concurrentes , como se señaló en el comentario de Tim Sylvester, y generará excepciones en caso de carreras. Para solucionarlo, la combinación de inserción / actualización debe estar envuelta en algún tipo de declaración de bucle, de modo que en caso de una excepción, todo se vuelva a intentar.

A modo de ejemplo, aquí se muestra cómo el código de Grommit se puede envolver en un bucle para que sea seguro cuando se ejecuta simultáneamente:

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: en el modo de transacción SERIALIZABLE , que no recomiendo por cierto, puede que se encuentre con ORA-08177: en su lugar, no puede serializar el acceso para las excepciones de esta transacción .

Answer 9

Otra alternativa sin la verificación de excepción:

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;

Answer 10

Prueba esto,

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

Answer 11

Una alternativa a MERGE (la "manera antigua"):

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;   

Answer 12

Una nota sobre las dos soluciones que sugieren:

1) Insertar, si la excepción luego actualizar,

o

2) Actualizar, si sql% rowcount = 0 entonces inserte

La cuestión de si insertar o actualizar primero también depende de la aplicación. ¿Esperas más inserciones o más actualizaciones? El que es más probable que tenga éxito debe ir primero.

Si escoges el incorrecto, obtendrás un montón de lecturas de índice innecesarias. No es un gran problema, pero sigue siendo algo a tener en cuenta.