oracle sql：如果存在则更新否则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4015199/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle sql: update if exists else insert
提问by Aks
Possible Duplicate:
Oracle: how to UPSERT (update or insert into a table?)
Hi,
你好，
I have a table in which a record has to be modified if it already exists else a new record has to be inserted.
Oracle sql doesnt accept IF EXISTS, otherwise I would have done an if - update - else - insertquery. I've looked at MERGEbut it only works for multiple tables. What do i do?
我有一个表，如果记录已经存在，则必须在其中修改记录，否则必须插入新记录。Oracle sql 不接受IF EXISTS，否则我会做一个if - update - else - insert查询。我看过，MERGE但它只适用于多个表。我该怎么办？
回答by Tony Andrews
MERGE doesn't need "multiple tables", but it does need a query as the source. Something like this should work:
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);
Alternatively you can do this in PL/SQL:
或者，您可以在 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;
回答by Adam Musch
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 [,...]);
回答by Adam Musch
You could use the SQL%ROWCOUNTOracle variable:
您可以使用SQL%ROWCOUNTOracle 变量：
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;
It would be easier just to determine if your primary key (i.e. field1) has a value and then perform an insert or update accordingly. That is, if you use said values as parameters for a stored procedure.
确定您的主键（即field1）是否具有值然后相应地执行插入或更新会更容易。也就是说，如果您使用所述值作为存储过程的参数。
回答by Adrian Smith
The way I always do it (assuming the data is never to be deleted, only inserted) is to
我总是这样做的方式（假设数据永远不会被删除，只会插入）是
- Firstly do an
insert, if this fails with a unique constraint violation then you know the row is there, - Then do an
update
- 首先做一个
insert，如果这因违反唯一约束而失败，那么您就知道该行在那里， - 然后做一个
update
Unfortunately many frameworks such as Hibernate treat all database errors (e.g. unique constraint violation) as unrecoverable conditions, so it isn't always easy. (In Hibernate the solution is to open a new session/transaction just to execute this one insertcommand.)
不幸的是，许多框架（例如 Hibernate）将所有数据库错误（例如，违反唯一约束）视为不可恢复的条件，因此并不总是那么容易。（在 Hibernate 中，解决方案是打开一个新的会话/事务来执行这个insert命令。）
You can't just do a select count(*) .. where ..as even if that returns zero, and therefore you choose to do an insert, between the time you do the selectand the insertsomeone else might have inserted the row and therefore your insertwill fail.
select count(*) .. where ..即使返回零，您也不能只执行 a ，因此您选择执行 an insert，在您执行 theselect和insert其他人可能已经insert编辑该行的时间之间，因此您insert将失败。
回答by Jokke Heikkil?
HC-way :)
HC 方式:)
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;
