Oracle: comment UPSERT (mettre à jour ou insérer dans une table?)

la déclaration de fusion fusionne les données entre deux tableaux. À l'aide de DOUBLE nous permet d'utiliser cette commande. Notez que ceci n'est pas protégé contre l'accès simultané.

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

le double exemple ci-dessus qui est en PL / SQL était grand parce que je voulais faire quelque chose de similaire, mais je voulais côté client ... donc voici le SQL que j'ai utilisé pour envoyer une déclaration similaire directement à partir de certains 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" )

cependant, du point de vue de C#, cela permet d'être plus lent que de faire la mise à jour et de voir si les lignes affectées étaient 0 et de faire l'insertion si elle l'était.

autre variante sans contrôle d'exception:

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. insérer s'il n'existe pas
2. mise à jour:

    
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;

aucune des réponses données jusqu'à présent N'est sûre face aux accès concurrents , comme le souligne le commentaire de Tim Sylvester, et soulèvera des exceptions en cas de courses. Pour corriger cela, le combo insert/update doit être enveloppé dans une sorte de boucle de déclaration, de sorte que dans le cas d'une exception toute la chose est retrieved.

à titre d'exemple, voici comment le code de Grommit peut être enveloppé dans une boucle pour le rendre sûr lorsqu'il est exécuté simultanément:

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. en mode transaction SERIALIZABLE , que je ne recommande pas btw, vous pourriez tomber sur ORA-08177: ne peut pas sérialiser l'accès pour ce mouvement exceptions à la place.

j'aimerais une réponse de Grommit, sauf qu'il faut des valeurs de dupe. J'ai trouvé la solution où il peut apparaître une fois: 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); 

Une remarque concernant les deux solutions, qui propose:

1) Insérer, si exception puis mettre à jour,

ou

2) la mise à Jour, si sql%rowcount = 0 alors insérer

la question de savoir s'il faut insérer ou mettre à jour en premier dépend également de l'application. Vous attendez plus d'encarts ou plus de mises à jour? Celui qui a le plus de chances de réussir devrait passer en premier.

Si vous choisissez le mauvais vous obtiendrez un tas d'inutiles indice de lit. Pas une affaire énorme, mais encore quelque chose à considérer.

j'utilise le premier échantillon depuis des années. Remarquez plutôt que de compter.

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;

le code ci-dessous est le code éventuellement nouveau et amélioré

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)

dans le premier exemple, la mise à jour effectue une recherche d'index. Il le faut, afin de mettre à jour la rangée de droite. Oracle ouvre un curseur implicite, et nous l'utilisons pour envelopper un correspondant insérez donc, nous savons que l'insert ne sera possible que lorsque la clé n'existe pas. Mais l' insert est une commande indépendante et doit effectuer une seconde recherche. Je ne connais pas le fonctionnement interne de la commande merge mais comme la commande est une unité unique, Oracle aurait pu exécuter la bonne insertion ou mise à jour avec une seule recherche d'index.

je pense que la fusion est préférable quand vous avez un traitement à faire qui signifie prendre des données de certaines tables et mettre à jour une table, peut-être insérer ou supprimer des lignes. Mais pour le cas Simple rangée, vous pouvez considérer le premier cas la syntaxe est la plus courante.

copier & coller exemple pour upserter une table dans une autre, avec Fusion:

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

résultat:

1. B45
2. c 3 3
3. 1 1

Essayez cette,

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

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

"Dans Oracle9i, un UPSERT peut accomplir cette tâche en une seule instruction:"

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;