Оракул: как для обновления и вставки (Update или INSERT в таблицу?)

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

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

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

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

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;

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

    
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;

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

в качестве примера, вот как код 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; 

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

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

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

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

или

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

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

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

я использовал первый пример кода в течение многих лет. Заметьте, не нашел, а не считать.

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

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

копировать и вставить пример для upserting одной таблицы в другую, с 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. b 4 5
2. c 3 3
3. в 1 1

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

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

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