Oracle SQL: 如果存在则更新，否则插入

Question

可能重复：
Oracle：如何进行UPSERT操作（更新或插入表格）？

你好，

我有一张表格，如果记录已经存在，则必须修改该记录，否则必须插入新记录。 Oracle sql不接受IF EXISTS，否则我将执行if-update-else-insert查询。我查看了MERGE，但它仅适用于多个表格。我该怎么办？

- Aks

Answer 1

MERGE不需要“多个表”，但它需要一个查询作为源。这样的语句应该可以工作：

MERGE INTO mytable d
USING (SELECT 1 id, 'x' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

或者您可以在 PL/SQL 中这样做：

BEGIN
  INSERT INTO mytable (id, name) VALUES (1, 'x');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE mytable
    SET    name = 'x'
    WHERE id = 1;
END;

- Tony Andrews

---

+1 我不了解MERGE指令，但是对于异常DUP_VAL_ON_INDEX处理，这绝对是一个好的解决方案，因为Oracle异常处理通常用于这种行为！=) - Will Marcouiller

+1；需要注意的是，替代方案通常效率要低得多。 - DCookie

当ID未知时该怎么做？例如，您通过姓名搜索行并想要更改姓氏... - Dumbo

@Dumbo，你应该在该列上设置唯一性约束吧？ - Yassin Hajaj

对于未来查看此线程的任何人，请注意，如果您实际上想在运行MERGE INTO语句后在OracleSQL数据库中看到结果，请确保通过运行COMMIT;提交事务。 - Vincent

Answer 2

merge into MY_TABLE tgt
using (select [expressions]
         from dual ) src
   on (src.key_condition = tgt.key_condition)
when matched then 
     update tgt
        set tgt.column1 = src.column1 [,...]
when not matched then 
     insert into tgt
        ([list of columns])
     values
        (src.column1 [,...]);

- Adam Musch

Answer 3

您可以使用SQL%ROWCOUNT Oracle变量：

UPDATE table1
  SET field2 = value2, 
      field3 = value3 
WHERE field1 = value1; 

IF (SQL%ROWCOUNT = 0) THEN 

  INSERT INTO table (field1, field2, field3)
  VALUES (value1, value2, value3);

END IF; 

如果您的主键（即field1）有值，那么确定后再执行插入或更新操作会更容易。也就是说，如果您将这些值用作存储过程的参数。

- SirGalahad

---

如果同时有多个会话在写入，你可能会遇到“更新”操作未触及任何行，因此你会假设没有行存在，需要进行“插入”操作；但与此同时，可能已经有人执行了“插入”，所以你的“插入”操作将因为违反唯一约束而失败。这就是为什么先执行“插入”操作（并捕获唯一约束冲突），再执行“更新”操作非常重要。请注意不要颠倒执行顺序。 - Adrian Smith

我见过的最糟糕的逻辑 - Vimal Bhaskar

Answer 4

我通常的做法（假设数据永远不会被删除，只会插入）是：

• 首先进行insert，如果由于唯一性约束而失败，则说明该行已存在，
• 然后执行update。

不幸的是，许多框架（如Hibernate）将所有数据库错误（例如唯一性约束）视为无法恢复的条件，因此这并不总是容易。 （在Hibernate中，解决方案是打开一个新的会话/事务来执行此一个insert命令。）

你不能只做一个select count(*) .. where ..，即使它返回零，并且你选择执行insert，在你执行select和insert之间，其他人可能已经insert了该行，因此你的insert将失败。

- Adrian Smith

---

即使使用了仅插入约束，如果有多个更新程序写入表格，使用两个事务也可能导致唯一性异常。 - David Mann

David Mann，很抱歉我没理解，请您澄清。 - Adrian Smith

嗨Adrian，我正在思考一种情况，即UPDATE语句仅在数据自尝试INSERT以来未更改的情况下才有效，并且有多个进程执行插入和更新操作。除非对表中的所有插入和更新进行同步，否则根据进程交错的方式，有时UPDATE可能成功，有时可能失败。对我来说，这似乎是一个特殊情况，直到我被要求执行仅在上次读取数据的状态仍然相同的情况下进行UPDATE操作。 - David Mann

Answer 5

HC-way :)

DECLARE
  rt_mytable mytable%ROWTYPE;
  CURSOR update_mytable_cursor(p_rt_mytable IN mytable%ROWTYPE) IS
  SELECT *
  FROM   mytable
  WHERE  ID = p_rt_mytable.ID
  FOR UPDATE;
BEGIN
  rt_mytable.ID   := 1;
  rt_mytable.NAME := 'x';
  INSERT INTO mytable VALUES (rt_mytable);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  <<update_mytable>>
  FOR i IN update_mytable_cursor(rt_mytable) LOOP
    UPDATE mytable SET    
      NAME = p_rt_mytable.NAME
    WHERE CURRENT OF update_mytable_cursor;
  END LOOP update_mytable;
END;

- Jokke Heikkilä

---

这没什么特别的，因为这个基本原理早就在之前发布过了。只是为了好玩 :) - Jokke Heikkilä

"HC" 的意思是什么？ - Nicolas Barbulesco

我也会在游标参数中添加IN OUT NOCOPY...但那是3年前我发布的 :) - Jokke Heikkilä

HC 的意思是 Hard Core。 - Sumant

Answer 6

如果您想在Oracle中使用UPSERT/MERGE命令，请参考this问题。否则，您可以先执行count(1)，然后决定是插入还是更新来解决客户端的问题。

- Pablo Santa Cruz

---

你的说法完全不正确；自从Oracle 9i版本起，就已经支持MERGE语句了。 - Adam Musch

交叉引用是好的；虽然它与你所说的相矛盾。不是我的-1，但我至少能够理解它。 - Jonathan Leffler