311 votos

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

El funcionamiento del UPSERT actualiza o inserta una fila en una tabla, dependiendo de si la tabla ya tiene una fila que coincide 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...

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

219voto

Mark Harrison Puntos 77152

El Declaración de MERGE fusiona los datos entre dos tablas. Usando DUAL nos permite usar este comando.

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

58 votos

Aparentemente la sentencia "merge into" no es atómica. Puede dar lugar a "ORA-0001: unique constraint" cuando se utiliza simultáneamente. La comprobación de la existencia de una coincidencia y la inserción de un nuevo registro no están protegidas por un bloqueo, por lo que existe una condición de carrera. Para hacer esto de forma fiable, es necesario capturar esta excepción y volver a ejecutar la fusión o hacer una simple actualización en su lugar. En Oracle 10, se puede utilizar la cláusula "log errors" para hacer que se continúe con el resto de las filas cuando se produce un error y se registre la fila infractora en otra tabla, en lugar de simplemente detenerse.

1 votos

Hola, he intentado utilizar el mismo patrón de consulta en mi consulta, pero de alguna manera mi consulta está insertando filas duplicadas. No puedo encontrar más información sobre la tabla DUAL. ¿Puede alguien decirme dónde puedo obtener información sobre DUAL y también sobre la sintaxis de fusión?

5 votos

@Shekhar Dual es una tabla ficticia con una sola fila y columnan adp-gmbh.ch/ora/misc/dual.html

114voto

Grommit Puntos 501

El ejemplo dual de arriba que está en PL/SQL fue genial porque quería hacer algo similar, pero lo quería del lado del cliente... así que aquí está el SQL que usé para enviar una sentencia 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 la perspectiva de C# esto proporciona ser más lento que hacer la actualización y ver si las filas afectadas eran 0 y hacer la inserción si lo eran.

11 votos

He vuelto aquí para ver de nuevo este patrón. Falla silenciosamente cuando se intentan inserciones concurrentes. Una inserción tiene efecto, la segunda fusión no inserta ni actualiza. Sin embargo, el enfoque más rápido de hacer dos declaraciones separadas es seguro.

3 votos

Los novatos en el mundo oral, como yo, pueden preguntarse qué es esto doble tabla ver esto : stackoverflow.com/q/73751/808698

6 votos

Lástima que con este patrón tengamos que escribir dos veces los datos (John, Smith...). En este caso, no gano nada utilizando MERGE y prefiero utilizar un método mucho más sencillo DELETE entonces INSERT .

66voto

Tony Andrews Puntos 67363

Una alternativa a la Fusión (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;

17 votos

El problema es que tienes una ventana entre la inserción y la actualización en la que otro proceso podría lanzar con éxito un borrado. Sin embargo, utilicé este patrón en una tabla que nunca tiene borrados disparados contra ella.

2 votos

Vale, estoy de acuerdo. No sé por qué no era obvio para mí.

4 votos

No estoy de acuerdo con Chotchki. "Duración de los bloqueos : Todos los bloqueos adquiridos por las sentencias dentro de una transacción se mantienen durante la duración de la misma, evitando interferencias destructivas, incluyendo lecturas sucias, actualizaciones perdidas y operaciones DDL destructivas de transacciones concurrentes." Fuente : enlace

50voto

Brian Schmitt Puntos 4246

Otra alternativa sin la comprobación de la 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;

1 votos

Su solución proporcionada no me funciona. ¿Funciona %rowcount sólo con cursores explícitos?

0 votos

¿Qué pasa si la actualización devuelve 0 filas modificadas porque el registro ya estaba allí y los valores eran los mismos?

10 votos

@Adriano: sql%rowcount seguirá devolviendo > 0 si la cláusula WHERE coincide con alguna fila, aunque la actualización no cambie realmente ningún dato de esas filas.

9voto

AnthonyVO Puntos 531

Una nota sobre las dos soluciones que sugieren:

1) Insertar, si es una excepción, actualizar,

o

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

La cuestión de si insertar o actualizar primero depende también de la aplicación. ¿Espera más inserciones o más actualizaciones? La que tenga más posibilidades de éxito debería ir primero.

Si escoges el equivocado, tendrás un montón de lecturas de índice innecesarias. No es gran cosa, pero es algo que hay que considerar.

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X