在Oracle SQL中执行MERGE操作时，如何更新源中未匹配的行？

Question

我有一个main数据库和一个report数据库，需要将main数据库中的一个表同步到report数据库。

然而，当在main数据库中删除一个项目时，我只想在report数据库中设置一个IsDeleted标志。

有没有一种优雅的方法来实现这个需求呢？

我目前正在使用MERGE语句，像这样：

MERGE INTO report.TEST target
USING (SELECT * FROM main.TEST) source
   ON (target.ID = source.ID)
WHEN MATCHED THEN
    UPDATE SET (target... = source...)
WHEN NOT MATCHED THEN
    INSERT (...) VALUES (source...)
;

WHEN NOT MATCHED语句可以从main表中获取所有的新值，但我也想更新来自report表中的所有旧值。

我正在使用Oracle PL/SQL。

- Scott Rippey

---

你想用哪些值来更新 main.test？你会从哪里获取它们？ - user330315

如果从 main.test 中删除了一行，则相应的 report.test 行将不匹配。我想为所有不匹配的行设置 report.test.IsDeleted 标志。 - Scott Rippey

我认为这不可能，因为基础连接（using (...)）的结果不会包含该行。那么这些数据应该从哪里来？ - user330315

那么，MERGE和LEFT OUTER JOIN相似吗？我猜我希望它也有一个FULL OUTER JOIN选项。 - Scott Rippey

我认为SQL Server 2008有一个WHEN NOT MATCHED ON SOURCE子句，可以执行FULL OUTER JOIN！我找不到太多关于它的文档，但我在Oracle中尝试了一下，没有成功，所以我想看看其他选项。 - Scott Rippey

Answer 1

你可以使用单独的UPDATE语句来完成这个操作。

UPDATE report.TEST target
SET    is Deleted = 'Y'
WHERE  NOT EXISTS (SELECT 1
                   FROM   main.TEST source
                   WHERE  source.ID = target.ID);

我不知道任何将此集成到您的MERGE语句中的方法。

- steve godfrey

---

似乎很奇怪我不能用MERGE来做这件事。我会考虑这个答案。 - Scott Rippey

如果我使用OUTER JOIN，并在main.test is null的情况下进行更新，是否会更加“优雅”/高效？ - Scott Rippey

这里有一个相关的问题 https://dev59.com/8W445IYBdhLWcg3wapzF，我认为它会执行外连接，而不是完全外连接，所以我不认为你提出的建议会起作用。 - steve godfrey

非常感谢，那个链接很有帮助。我了解到：如果MERGE只有WHEN MATCHED THEN，它会使用普通的JOIN。如果有WHEN NOT MATCHED THEN，则使用LEFT OUTER JOIN。现在，我希望有一个类似于WHEN NOT MATCHED ON SOURCE THEN的功能，可以执行FULL OUTER JOIN。但这可能仅适用于SQL Server，而不是Oracle。 - Scott Rippey

Answer 2

下面的答案是关于将数据合并到同一张表中的：

MERGE INTO YOUR_TABLE d
USING (SELECT 1 FROM DUAL) m
    ON ( d.USER_ID = '123' AND d.USER_NAME= 'itszaif') 
WHEN NOT MATCHED THEN
        INSERT ( d.USERS_ID, d.USER_NAME)
        VALUES ('123','itszaif');

该命令检查USER_ID和USER_NAME是否匹配，如果不匹配，则进行插入操作。

- Zafrullah Syed

Answer 3

MERGE INTO target
USING
(
    --Source data
    SELECT id, some_value, 0 deleteMe FROM source
    --And anything that has been deleted from the source
    UNION ALL
    SELECT id, null some_value, 1 deleteMe
    FROM
    (
        SELECT id FROM target
        MINUS
        SELECT id FROM source
    )
) source
   ON (target.ID = source.ID)
WHEN MATCHED THEN
    --Requires a lot of ugly CASE statements, to prevent updating deleted data
    UPDATE SET target.some_value =
        CASE WHEN deleteMe=1 THEN target.some_value ELSE source.some_value end
    ,isDeleted = deleteMe
WHEN NOT MATCHED THEN
    INSERT (id, some_value, isDeleted) VALUES (source.id, source.some_value, 0)

--Test data
create table target as
select 1 ID, 'old value 1' some_value, 0 isDeleted from dual union all
select 2 ID, 'old value 2' some_value, 0 isDeleted from dual;

create table source as
select 1 ID, 'new value 1' some_value, 0 isDeleted from dual union all
select 3 ID, 'new value 3' some_value, 0 isDeleted from dual;


--Results:
select * from target;

ID  SOME_VALUE   ISDELETED
1   new value 1  0
2   old value 2  1
3   new value 3  0

- Jon Heller

---

谢谢你的创意！我不会这样做，但它确实完成了工作。 - Scott Rippey

Answer 4

merge into x as target using y as Source on target.ID = Source.ID
when not matched by target then insert
when matched then update
when not matched by source and target.ID is not null then
update whatevercolumn = 'isdeleted' ;

- Mark

---

你在哪里找到这个语法的？(by target) - Florin Ghita

这正是我正在寻找的...但是，正如@FlorinGhita所说，你在哪里找到这个语法？这是MS SQL语法吗？ - Scott Rippey

由于这是SQL Server合并语法，而不是Oracle合并，因此为了准确性，请注意区分。 - user272735

仅供大家参考，这是一个不错的回答，但是“not matched by target”和“not matched by source”是仅由MS SQL Server支持的功能。该功能可以解决我的问题，所以我希望Oracle SQL也能支持它。 - Scott Rippey