Oracle: como UPSERT (atualizar ou inserir em uma tabela?)

A operação UPSERT atualiza ou insere uma linha em uma tabela, dependendo se a tabela já tiver uma linha que corresponda aos dados:

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

Como a Oracle não possui uma instrução UPSERT específica, qual é a melhor maneira de fazer isso?

   

Uma alternativa para MERGE (a “maneira antiga”):

 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; 

A instrução MERGE mescla dados entre duas tabelas. Usando o DUAL nos permite usar este comando. Observe que isso não está protegido contra access simultâneo.

 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; AB ---------------------- ---------------------- 10 2 20 1 

O exemplo duplo acima que está em PL / SQL foi ótimo porque eu queria fazer algo semelhante, mas eu queria que o lado do cliente … então aqui é o SQL que eu usei para enviar uma declaração semelhante direto de alguns 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" ) 

No entanto, a partir de uma perspectiva C #, isso proverá ser mais lento do que fazer a atualização e ver se as linhas afetadas eram 0 e fazer a inserção, se fosse.

Outra alternativa sem a verificação de exceção:

 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. insira se não existe
  2. atualizar:
    
 INSERT INTO mytable (id1, t1) 
   SELECIONE 11, 'x1' DE DUAL 
   ONDE NÃO EXISTE (SELECT id1 FROM mytble WHERE id1 = 11); 

 ATUALIZAÇÃO mytable SET t1 = 'x1' ONDE id1 = 11;

Nenhuma das respostas dadas até agora é segura em face de accesss simultâneos , como apontado no comentário de Tim Sylvester, e levantará exceções no caso de corridas. Para corrigir isso, a combinação de inserção / atualização deve ser envolvida em algum tipo de instrução de loop, de modo que, no caso de uma exceção, a coisa toda seja tentada novamente.

Por exemplo, aqui está como o código de Grommit pode ser empacotado em um loop para torná-lo seguro quando executado simultaneamente:

 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, ie continue looping WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted NULL; -- exception? -> no op, ie continue looping END; END LOOP; END; 

NB No modo de transação SERIALIZABLE , que eu não recomendo btw, você pode encontrar o ORA-08177: não é possível serializar o access para esta exceção de transação .

Eu gostaria de responder Grommit, exceto que exigem valores de dupe. Eu encontrei a solução onde ela pode aparecer uma vez: 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); 

Uma nota sobre as duas soluções que sugerem:

1) Inserir, se exceção, em seguida, atualizar,

ou

2) Atualizar, se sql% rowcount = 0, em seguida, insira

A questão de inserir ou atualizar primeiro é também dependente do aplicativo. Você está esperando mais inserções ou mais atualizações? Aquele que tem maior probabilidade de sucesso deve ir primeiro.

Se você escolher o errado, você receberá um monte de leituras de índice desnecessárias. Não é um grande negócio, mas ainda algo a considerar.

Eu tenho usado a primeira amostra de código por anos. Observe não encontrado em vez de contar.

 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; 

O código abaixo é o código possivelmente novo e melhorado

 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) 

No primeiro exemplo, a atualização faz uma pesquisa de índice. Tem que, a fim de atualizar a linha direita. O Oracle abre um cursor implícito, e nós o usamos para quebrar uma inserção correspondente, então sabemos que a inserção só acontecerá quando a chave não existir. Mas a inserção é um comando independente e tem que fazer uma segunda pesquisa. Eu não sei o funcionamento interno do comando de mesclagem, mas como o comando é uma única unidade, o Oracle poderia ter executado a inserção ou atualização correta com uma única pesquisa de índice.

Eu acho que a mesclagem é melhor quando você tem algum processamento a ser feito, isso significa pegar dados de algumas tabelas e atualizar uma tabela, possivelmente inserindo ou excluindo linhas. Mas, para o caso de linha única, você pode considerar o primeiro caso, já que a syntax é mais comum.

Copie e cole o exemplo para transformar uma tabela em outra, com 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 

Resultado:

  1. b 4 5
  2. c 3 3
  3. um 1 1

Tente isso

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

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

“No Oracle9i, um UPSERT pode realizar essa tarefa em uma única instrução:”

 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;