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

Операция 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, как лучше всего это сделать?

sql oracle merge upsert

26.10.2008 04:24

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

305

444 969

Перейти к ответу Данный вопрос помечен как решенный

Ответы 12

Заявление 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

Очевидно, оператор «слияния с» не является атомарным. Это может привести к появлению «ORA-0001: уникальное ограничение» при одновременном использовании. Проверка наличия совпадения и вставка новой записи не защищены блокировкой, поэтому возникает состояние гонки. Чтобы сделать это надежно, вам нужно перехватить это исключение и либо повторно запустить слияние, либо вместо этого выполнить простое обновление. В Oracle 10 вы можете использовать условие «журнала ошибок», чтобы продолжить работу с остальными строками, когда возникает ошибка, и записывать ошибочную строку в другую таблицу, а не просто останавливаться.

— 13.07.2009 09:15

Привет, я пытался использовать тот же шаблон запроса в своем запросе, но каким-то образом мой запрос вставляет повторяющиеся строки. Я не могу найти дополнительную информацию о таблице DUAL. Кто-нибудь может сказать мне, где я могу получить информацию о DUAL, а также о синтаксисе слияния?

— 15.10.2010 10:12

@Shekhar Dual - это фиктивная таблица с одной строкой и столбцом adp-gmbh.ch/ora/misc/dual.html

— 19.11.2010 07:56

Благодарность! Небольшая опечатка в вашем при совпадении, затем обновите, у вас должен быть псевдоним: update set m.b = ..

— 11.08.2011 21:30

@TimSylvester - Oracle использует транзакции, поэтому гарантирует, что моментальный снимок данных в начале транзакции будет согласованным на протяжении всей транзакции, за исключением любых изменений, внесенных в него. Параллельные вызовы базы данных используют стек отмены; поэтому Oracle будет управлять конечным состоянием в зависимости от порядка начала / завершения параллельных транзакций. Таким образом, у вас никогда не будет состояния гонки, если проверка ограничений выполняется перед вставкой, независимо от того, сколько одновременных вызовов выполняется к одному и тому же коду SQL. В худшем случае вы можете вызвать много споров, и Oracle потребуется гораздо больше времени, чтобы достичь окончательного состояния.

— 06.11.2012 17:23

дальнейшее чтение по upserts с использованием слияния: idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_5.shtml

— 03.05.2014 10:39

Чтобы сделать это на 100% правильным, предложение «при совпадении» должно стоять перед «при несоответствии», ср. docs.oracle.com/cd/B19306_01/server.102/b14200/…

— 06.02.2015 19:38

Мне не ясно, что произойдет, если вы поместите обработчик исключений вокруг оператора слияния. Если вы перехватите ORA-00001 и скажете «then null», вставляет ли он оставшуюся часть строк, которые НЕ могут генерировать исключения? Или при первом исключении тихонько выручит? Вы же не предлагаете обернуть исключение ВНУТРИ оператора слияния только вокруг предложения INSERT?

— 12.02.2015 17:53

@RandyMagruder Это тот случай, когда наступил 2015 год, и мы все еще не можем надежно выполнить апсерт в Oracle! Знаете ли вы о параллельном безопасном решении?

— 10.04.2015 01:23

Не достоверно. Я закончил с циклами повтора в клиентском коде. :(

— 27.08.2015 19:05

@Roland Из той же ссылки, которую вы опубликовали: You can specify this [the merge_update_clause] clause by itself or with the merge_insert_clause. If you specify both, then they can be in either order., поэтому порядок совпадений / несоответствий является произвольным.

— 14.01.2016 20:07

@Shekhar Где вы искали и не нашли информации о двойном столе?

— 04.03.2020 12:34

26.10.2008 04:24

Ответ принят как подходящий

Альтернатива 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;

@chotchki: правда? Объяснение было бы полезно.

— 29.09.2011 12:51

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

— 30.09.2011 02:50

Хорошо я согласен. Не знаю, почему для меня это было неочевидно.

— 30.09.2011 15:22

Я не согласен с Чотчки. «Длительность блокировки: все блокировки, полученные операторами внутри транзакции, удерживаются на время транзакции, предотвращая деструктивное вмешательство, включая грязное чтение, потерянные обновления и деструктивные операции DDL из параллельных транзакций». Источник: связь

— 28.10.2014 11:45

@yohannc: Я думаю, дело в том, что мы не получили никаких блокировок просто из-за того, что пытались вставить строку, но не смогли.

— 28.10.2014 12:49

Люблю отступы и строчные буквы. Желаю, чтобы это стало стандартом в мире Oracle.

— 28.03.2020 10:37

27.10.2008 14:12

Другой вариант проверки без исключения:

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;

Ваше решение не работает для меня. % Rowcount работает только с явными курсорами?

— 31.01.2011 04:22

Что, если обновление вернуло 0 строк, измененных, потому что запись уже была там и значения были такими же?

— 01.08.2011 18:40

@Adriano: sql% rowcount по-прежнему будет возвращать> 0, если предложение WHERE соответствует любым строкам, даже если обновление фактически не изменяет какие-либо данные в этих строках.

— 24.08.2011 14:39

Не работает: PLS-00207: идентификатор COUNT, примененный к неявному SQL-курсору, не является допустимым атрибутом курсора

— 27.09.2013 19:57

Ошибки синтаксиса здесь :(

— 12.11.2019 14:34

28.10.2008 17:37

От 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;

-1 Типичный Дон Бурлесон cr @ p Боюсь - это вставка в ту или иную таблицу, здесь нет никакого "апсерта"!

— 24.08.2011 14:36

10.02.2010 04:24

Двойной пример выше, который находится в 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, и выполнение вставки, если это было.

Я вернулся сюда, чтобы еще раз проверить этот паттерн. Он терпит неудачу при попытке одновременной вставки. Одна вставка вступает в силу, вторая слияние не вставляет и не обновляет. Однако более быстрый подход к выполнению двух отдельных операторов безопасен.

— 18.05.2011 10:27

Новички в oralcle, вроде меня, могут спросить, что это за таблица двойной, посмотрите это: stackoverflow.com/q/73751/808698

— 20.08.2012 20:44

Жаль, что с этим шаблоном нам нужно написатьдважды данных (Джон, Смит ...). В этом случае я ничего не выиграю, используя MERGE, и я предпочитаю использовать гораздо более простой DELETE, чем INSERT.

— 15.11.2013 20:17

@NicolasBarbulesco, этот ответ не требует двойной записи данных: stackoverflow.com/a/4015315/8307814

— 11.11.2019 12:14

@NicolasBarbulesco MERGE INTO mytable d USING (SELECT 1 id, 'x' name from dual) s ON (d.id = s.id) WHEN MATCHED THEN UPDATE SET d.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

— 11.11.2019 12:14

22.04.2010 20:00

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

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

15.03.2011 06:34

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

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

или же

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

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

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

sql% notfound - мое личное предпочтение

— 12.01.2015 19:56

26.11.2011 03:19

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

Вы имели ввиду INSERT (B.CILT, B.SAYFA, B.KUTUK, B.MERNIS_NO) VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO); ?

— 03.04.2015 10:36

К счастью, вы отредактировали свой ответ! :) моя правка была, к сожалению, отвергнута stackoverflow.com/review/suggested-edits/7555674

— 03.04.2015 17:20

25.12.2012 19:36

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

    
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;

23.01.2014 18:02

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

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

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;

N.B. В режиме транзакции SERIALIZABLE, который, кстати, я не рекомендую, вы можете столкнуться с ORA-08177: невозможно сериализовать доступ для этой транзакции вместо этого.

Превосходно! Наконец, безопасный ответ при одновременном доступе. Есть ли способ использовать такую конструкцию от клиента (например, от клиента Java)?

— 25.04.2014 11:17

Вы имеете в виду отсутствие вызова хранимой процедуры? Что ж, в этом случае вы также можете просто перехватить определенные исключения Java и повторить попытку в цикле Java. В Java это чертовски удобнее, чем в Oracle SQL.

— 25.04.2014 12:15

Извините: я недостаточно конкретизировал. Но вы правильно поняли. Я подал в отставку, чтобы сделать то, что вы только что сказали. Но я не удовлетворен на 100%, потому что он генерирует больше SQL-запросов, больше обратных обращений клиент / сервер. Это не лучшее решение с точки зрения производительности. Но моя цель - позволить Java-разработчикам моего проекта использовать мой метод для обновления в любой таблице (я не могу создать одну хранимую процедуру PLSQL для каждой таблицы или одну процедуру для каждого типа обновления).

— 25.04.2014 17:26

@Sebien Я согласен, было бы лучше, если бы это было инкапсулировано в области SQL, и я думаю, что вы можете это сделать. Я просто не предлагаю вам это выяснить ... :) Кроме того, на самом деле эти исключения, вероятно, будут происходить реже одного раза в синюю луну, поэтому вы не должны видеть влияния на производительность в 99,9% случаев. За исключением, конечно, нагрузочного тестирования ...

— 28.04.2014 03:59

01.04.2014 09:45

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

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

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

12.01.2015 19:48

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

Результат: