Вставить, если не существует оракул

Мне нужно иметь возможность запускать запрос Oracle, который вставляет несколько строк, но также проверяет, существует ли первичный ключ и если он это делает, тогда он пропускает эту вставку. Что-то вроде:

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;

Возможно ли это с Oracle?

_{Бонусные баллы, если вы можете сказать мне, как это сделать в PostgreSQL или MySQL.}

Ответ 1

Оператор называется MERGE. Посмотри, я слишком ленив.

Остерегайтесь, однако, что MERGE не является атомарным, что может вызвать следующий эффект (спасибо, 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

Ответ 2

Поздняя вечеринка, но...

С помощью oracle 11.2.0.1 есть семантический намек, который может сделать это: IGNORE_ROW_ON_DUPKEY_INDEX

Пример:

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

UPDATE. Хотя этот подсказку работает (если вы правильно произносите его), существуют более эффективные подходы, для которых не требуется Oracle 11R2:

Первый подход Прямой перевод семантического намека:

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

Второй aproach - намного быстрее, чем оба вышеупомянутых намека, когда есть много споров:

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;

Ответ 3

Это только вставляет, если элемент, который нужно вставить, еще не присутствует.

Работает так же, как:

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

в T-SQL

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

может быть не очень красивым, но он удобен:)

Ответ 4

Если вы НЕ хотите сливаться с другой таблицей, а скорее вставляете новые данные... Я придумал это. Возможно, лучший способ сделать это?

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

Ответ 5

Мы можем объединить DUAL и NOT EXISTS для архивирования вашего требования:

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

Ответ 6

Этот код находится на клиенте, тогда у вас много поездок на сервер, чтобы устранить это.

Вставьте все данные во временную таблицу, скажем, T с той же структурой, что и myFoo

Тогда

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

Это также должно работать и с другими базами данных - я сделал это на Sybase

Это не самое лучшее, если только очень немногие из новых данных должны быть вставлены, поскольку вы скопировали все данные по проводу.

Ответ 7

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;

Я использовал код выше. Это длинный, но простой и сработавший для меня. Похоже, с кодом Micheal.

Ответ 8

Это ответ на комментарий, отправленный erikkallen:

Вам не нужна временная таблица. если ты только несколько строк, (SELECT 1 FROM двойной UNION SELECT 2 FROM dual) будет делать. Почему ваш пример даст ORA-0001? Не объединить блокировка обновления для ключа индекса, а не до тех пор, пока Sess1 не будет совершенных или откат? - erikkallen

Хорошо, попробуйте сами и скажите мне, есть ли у вас такая же ошибка или нет:

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

Ответ 9

Если ваша таблица является "независимой" от других (я имею в виду, она не будет запускать каскадное удаление или не будет устанавливать отношения внешних ключей к нулю), хороший трюк может состоять в том, чтобы сначала УДАЛИТЬ строку, а затем ВСТАВИТЬ ее снова, Это может выглядеть так:

УДАЛИТЬ ИЗ MyTable WHERE prop1 = 'aaa';//предполагая, что он выберет не более одной строки!

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

Если вы удаляете что-то, чего не существует, ничего не произойдет.

Ответ 10

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;