如何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语句，那么最好的方法是什么呢?

当前回答

试试这个,

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

2011-03-15 03:34:15

其他回答

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;

2008-10-27 11:12:13

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

2010-02-10 01:24:03

试试这个,

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

2011-03-15 03:34:15

关于两种解决方案的说明如下:

1)插入，如果异常则更新，

2)更新，如果sql%rowcount = 0，则插入

先插入还是先更新的问题也取决于应用程序。您是否期待更多的插入或更新?最有可能成功的应该先走。

如果你选错了，你会得到一堆不必要的索引读取。不是什么大问题，但仍然值得考虑。

2011-11-25 23:19:47

我想要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);

2012-12-25 15:36:00

如何UPSERT(更新或插入到表中?)

推荐文章

最新文章

标签