[sql] 오라클 : UPSERT 방법 (테이블에 업데이트 또는 삽입?)

Program / 글쓴이

UPSERT 조작은 테이블에 이미 데이터와 일치하는 행이 있는지에 따라 테이블에 행을 갱신하거나 삽입합니다.

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

오라클은 특정 UPSERT 문을 가지고 있지 않으므로이를 수행하는 가장 좋은 방법은 무엇입니까?

답변

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;

답변

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

답변

PL / SQL에있는 위의 이중 예제는 비슷한 것을하고 싶었 기 때문에 훌륭했기 때문에 클라이언트 측을 원했습니다 … 그래서 일부 C #에서 직접 비슷한 문을 보내는 데 사용한 SQL이 있습니다.

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. 최신 정보:
mytable에 삽입 (id1, t1)
  11에서 'x1'을 선택하십시오.
  존재하지 않는 곳 (mytble에서 id1 = 11 선택);

업데이트 mytable SET t1 = 'x1'어디서 id1 = 11;

답변

팀 실베스터 (Tim Sylvester)의 의견에서 지적한 것처럼 지금까지의 답변 중 어느 것도 동시 액세스에 대해 안전 하지 않으며 레이스의 경우 예외를 제기하지 않습니다. 이를 수정하려면 삽입 / 업데이트 콤보를 일종의 루프 명령문으로 랩핑해야 예외의 경우 전체가 재 시도됩니다.

예를 들어, 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이 발생할 수 있습니다 . 대신 이 트랜잭션 예외에 대한 액세스를 직렬화 할 수 없습니다 .

답변

중복 값이 ​​필요한 것을 제외하고 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);