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.