Oracle: как сделать UPSERT (обновить или вставить в таблицу?)

Question

Операция UPSERT обновляет или вставляет строку в таблицу, в зависимости от того, есть ли в таблице строка, соответствующая данным:

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

Поскольку у Oracle нет конкретного оператора UPSERT, каков наилучший способ сделать это?

Answer 1

Альтернатива MERGE ("старомодный способ"):

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 2

Оператор MERGE объединяет данные между двумя таблицами. Использование DUAL позволяет нам использовать эту команду. Обратите внимание, что это не защищено от одновременного доступа.

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 3

Двойной пример, приведенный выше, который находится в PL/SQL, был великолепен, потому что я хотел сделать что-то подобное, но я хотел, чтобы это было на стороне клиента... так вот SQL, который я использовал для отправки аналогичного оператора прямо из некоторого 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" )

Однако с точки зрения C# это обеспечивает более медленную скорость, чем выполнение обновления и определение, было ли затронуто строк 0, и выполнение вставки, если это так.

Answer 4

Еще одна альтернатива без проверки исключений:

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 5

1. вставить, если не существует
2. Обновить:

    
INSERT INTO mytable (id1, t1) 
  ВЫБЕРИТЕ 11, 'x1' ОТ ДВОЙНОГО 
  ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ id1 ИЗ mytble WHERE id1 = 11); 

ОБНОВЛЕНИЕ mytable SET t1 = 'x1' ГДЕ id1 = 11;

Answer 6

Ни один из ответов, данных до сих пор, не является безопасным перед лицом одновременного доступа, как указано в комментарии Тима Сильвестра, и не вызовет исключений в случае гонок. Чтобы исправить это, комбинация вставки / обновления должна быть заключена в какой-то оператор цикла, чтобы в случае исключения все повторялось.

Например, вот как код Grommit может быть заключен в цикл, чтобы сделать его безопасным при одновременном запуске:

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 В режиме транзакции SERIALIZABLE, что я не рекомендую, кстати, вы можете столкнуться с ORA-08177: не может сериализовать доступ для этой исключительной ситуации транзакции.

Answer 7

Я бы хотел, чтобы Grommit ответил, за исключением того, что он требует двойных значений. Я нашел решение, где оно может появиться один раз: 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

Я использовал первый пример кода за годы. Обратите внимание не найден, а считать.

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;

Код ниже является возможно новым и улучшенным кодом

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)

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

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

Answer 9

Примечание относительно двух решений, которые предлагают:

1) Вставьте, если исключение, затем обновить,

или же

2) Обновить, если sql%rowcount = 0, затем вставить

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

Если вы выберете неправильный вариант, вы получите кучу ненужных индексов. Не огромная сделка, но все еще кое-что, чтобы рассмотреть.

Answer 10

Пример копирования и вставки для переноса одной таблицы в другую с помощью 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

Результат:

1. б 4 5
2. с 3 3
3. 1 1

Answer 11

Попробуй это,

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 12

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

"В Oracle9i UPSERT может выполнить эту задачу в одном выражении:"

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;