portable - sql developer mac

insertar si no existe el oráculo

Question

Necesito poder ejecutar una consulta de Oracle que vaya a insertar varias filas, pero también comprueba si existe una clave principal y, si lo hace, se salta esa inserción. Algo como:

INSERT ALL
    IF NOT EXISTS( SELECT 1 WHERE fo.primary_key=''bar'' )
    (
        INSERT INTO 
            schema.myFoo fo ( primary_key, value1, value2 )
        VALUES
            (''bar'',''baz'',''bat'')
    ),

    IF NOT EXISTS( SELECT 1 WHERE fo.primary_key=''bar1'' )
    (
        INSERT INTO 
            schema.myFoo fo ( primary_key, value1, value2 )
        VALUES
            (''bar1'',''baz1'',''bat1'')
    )
SELECT * FROM schema.myFoo;

¿Es esto posible con Oracle?

_{Puntos de bonificación si me puede decir cómo hacerlo en PostgreSQL o MySQL.}

Answer 1

Esta es una respuesta al comentario publicado por erikkallen:

No necesitas una tabla temporal. Si solo tiene algunas filas, (SELECT 1 FROM dual UNION SELECT 2 FROM dual) funcionará. ¿Por qué tu ejemplo daría ORA-0001? ¿No fusionaría tomaría el bloqueo de actualización en la clave de índice y no continuaría hasta que Sess1 se haya comprometido o revertido? - erikkallen

Bueno, pruébalo tú mismo y dime si recibes el mismo error o no:

SESS1:

create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;

SESS2: insert into t1 values(2, 2);

SESS1:

MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);

SESS2: commit;

SESS1: ORA-00001

Answer 2

Esto solo se inserta si el elemento que se va a insertar ya no está presente.

Funciona igual que:

if not exists (...) insert ... 

en T-SQL

insert into destination (DESTINATIONABBREV) 
  select ''xyz'' from dual 
  left outer join destination d on d.destinationabbrev = ''xyz'' 
  where d.destinationid is null;

puede no ser bonita, pero es útil :)

Answer 3

La declaración se llama MERGE. Búscalo, soy muy flojo.

Tenga cuidado, sin embargo, que MERGE no es atómico, lo que podría causar el siguiente efecto (gracias, Marius):

SESS1:

create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;

SESS2: insert into t1 values(2, 2);

SESS1:

MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);

SESS2: commit;

SESS1: ORA-00001

Answer 4

Llegando tarde a la fiesta, pero ...

Con Oracle 11.2.0.1 hay una sugerencia semántica que puede hacer esto: IGNORE_ROW_ON_DUPKEY_INDEX

Ejemplo:

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(customer_orders,pk_customer_orders) */
  into customer_orders
       (order_id, customer, product)
values (    1234,     9876,  ''K598'')
     ;

ACTUALIZACIÓN : aunque esta sugerencia funciona (si la deletreas correctamente), hay mejores enfoques que no requieren Oracle 11R2:

Primera aproximación-traducción directa de la sugerencia semántica anterior:

begin
  insert into customer_orders
         (order_id, customer, product)
  values (    1234,     9876,  ''K698'')
  ;
  commit;
exception
  when DUP_VAL_ON_INDEX
  then ROLLBACK;
end;

Segundo enfoque, mucho más rápido que las dos sugerencias anteriores cuando hay mucha controversia:

begin
    select count (*)
    into   l_is_matching_row
    from   customer_orders
    where  order_id = 1234
    ;

    if (l_is_matching_row = 0)
    then
      insert into customer_orders
             (order_id, customer, product)
      values (    1234,     9876,  ''K698'')
      ;
      commit;
    end if;
exception
  when DUP_VAL_ON_INDEX
  then ROLLBACK;
end;

Answer 5

Podemos combinar DUAL y NOT EXISTS para archivar su requerimiento:

INSERT INTO schema.myFoo ( 
    primary_key, value1, value2
) 
SELECT
    ''bar'', ''baz'', ''bat'' 
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 
    FROM schema.myFoo
    WHERE primary_key = ''bar''
);

Answer 6

Si NO desea fusionarse desde otra tabla, sino insertar datos nuevos ... se me ocurrió esto. ¿Hay quizás una mejor manera de hacer esto?

MERGE INTO TABLE1 a
    USING DUAL
    ON (a.C1_pk= 6)
WHEN NOT MATCHED THEN
    INSERT(C1_pk, C2,C3,C4)
    VALUES (6, 1,0,1);

Answer 7

Si ese código está en el cliente, entonces tienes muchos viajes al servidor para eliminar eso.

Inserta todos los datos en una tabla temportary di T con la misma estructura que myFoo

Entonces

insert myFoo
  select *
     from t
       where t.primary_key not in ( select primary_key from myFoo) 

Esto también debería funcionar en otras bases de datos. He hecho esto en Sybase.

No es lo mejor si se insertan muy pocos de los nuevos datos, ya que ha copiado todos los datos a través del cable.

Answer 8

Si su tabla es "independiente" de otras (quiero decir, no activará una eliminación en cascada o no establecerá ninguna relación de claves foráneas con nulo), un buen truco podría ser primero BORRAR la fila y luego INSERTARla de nuevo. Podría ser así:

ELIMINAR FROM MyTable WHERE prop1 = ''aaa''; // ¡suponiendo que seleccionará como máximo una fila!

INSERT INTO MyTable (prop1, ...) VALUES (''aaa'', ...);

Si estás eliminando algo que no existe, nada sucederá.

Answer 9

INSERT INTO schema.myFoo ( primary_key , value1 , value2 ) SELECT ''bar1'' AS primary_key ,''baz1'' AS value1 ,''bat1'' AS value2 FROM DUAL WHERE (SELECT 1 AS value FROM schema.myFoo WHERE LOWER(primary_key) =''bar1'' AND ROWNUM=1) is null;

Answer 10

DECLARE
   tmp NUMBER(3,1);
BEGIN
  SELECT COUNT(content_id) INTO tmp FROM contents WHERE (condition);
  if tmp != 0 then
    INSERT INTO contents VALUES (...);
  else
    INSERT INTO contents VALUES (...);
  end if;
END;

Usé el código de arriba. Es largo, pero, simple y funcionó para mí. Similar, al código de Micheal.