Oracle MERGE NOT MATCHED THEN UPDATE 可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45619857/
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
Is Oracle MERGE NOT MATCHED THEN UPDATE possible?
提问by Matthew Walk
We'd like to set the IS_DEL bit = 1 when a record exists in a Target table that doesn't exist in the Source table.
当源表中不存在的目标表中存在记录时，我们希望将 IS_DEL 位设置为 1。
Is it possible to use a MERGE statement using the WHEN NOT MATCHED clause, but make it perform an UPDATE?
是否可以使用使用 WHEN NOT MATCHED 子句的 MERGE 语句，但使其执行更新？
When attempting to do so, I'm getting a "ORA-00905: missing keyword" message.
尝试这样做时，我收到“ORA-00905：缺少关键字”消息。
MERGE
INTO AMEPSA.ENTERPRISE_LOCATION trg
USING (
SELECT C.LOCATION_KEY as LOCATION_KEY
FROM AMEPSA.ENTERPRISE_LOCATION C
INNER JOIN AMESTAGE.VW_LOCATION L ON C.REC_SRC_KEY_CD = L.LOCATION_ID
WHERE C.CURR_REC_IND = 'Y'
) src
ON (trg.LOCATION_KEY = src.LOCATION_KEY)
WHEN NOT MATCHED THEN UPDATE
SET trg.IS_DEL = 1
Does the "WHEN NOT MATCH" clause only support "THEN INSERT"?
“WHEN NOT MATCH”子句是否只支持“THEN INSERT”？
回答by Alex Poole
从文档：
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
使用 MERGE 语句从一个或多个源中选择行以更新或插入到表或视图中。您可以指定条件来确定是更新还是插入到目标表或视图中。
The syntax looks for rows in the source table (src) which do or do not have matching rows in the target table (trg). If there is a matching target row then it updates that; if there is not a matching row then it inserts a new row in the target table.
该语法在源表 ( src) 中查找在目标表 ( ) 中有或没有匹配行的行trg。如果有匹配的目标行，则更新它；如果没有匹配的行，那么它会在目标表中插入一个新行。
It does not, and cannot, look for rows in the target table that are not matched in the source table - which is what you are trying to identify and update.
它不会也不能在目标表中查找与源表中不匹配的行 - 这就是您要识别和更新的行。
The syntax diagrams for WHEN MATCHEDand WHEN NOT MATCHEDalso make it clear that you cannot do WHEN NOT MATCHED THEN UPDATE.
对于语法图WHEN MATCHED和WHEN NOT MATCHED也说清楚，你不能这样做WHEN NOT MATCHED THEN UPDATE。
回答by user7294900
Yes you can only insert when not match. See exact options in oracle merge.
是的，您只能在不匹配时插入。查看oracle merge 中的确切选项。
