sql-server - 与源不匹配时合并

Question

我有一个表“groups”，里面有一些数据:

groupid     groupname       grouptext
1           Cars            Toyota
2           Cars            BMW
3           Cars            Renault
4           Bikes           BMW
5           Bikes           Yamaha

之后，我是以下 SQL 语句，其中我试图使用 MERGE 语句更新组表。我想要做的是用 XML 中的数据更新组表。但我也想删除表中组名存在于 XML 中的所有条目。因此，例如在执行后我的表应该是这样的

groupid     groupname       grouptext
1           Cars            Audi
4           Bikes           BMW
5           Bikes           Yamaha
6           Singers         Lady Gaga

因为有 groupname，并且里面有汽车，所以所有的汽车都应该从表中删除。现在，当我使用 WHEN NOT MATCHED BY SOURCE AND 子句时，出现此错误:

Msg 5334, Level 16, State 2, Line 60
The identifier 's.groupname' cannot be bound. Only target columns and columns in the clause scope are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement.
Msg 5334, Level 16, State 2, Line 61
The identifier 's.grouptext' cannot be bound. Only target columns and columns in the clause scope are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement.

我的问题是，如何使用以下代码实现预期的结果:

DECLARE @UpdateXml Xml = N'<groups>
    <group>
        <groupid>1</groupid>
        <groupname>Cars</groupname>
        <grouptext>Audi</grouptext>
    </group>
    <group>
        <groupid>0</groupid>
        <groupname>Singers</groupname>
        <grouptext>Lady Gaga</grouptext>
    </group>
</groups>';

DECLARE @hDoc INT;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @UpdateXml;

MERGE INTO groups AS t
USING
(
    SELECT groupid, groupname, grouptext
    FROM OPENXML(@hdoc, '/groups/group', 1)
    WITH
    (
        groupid INT 'groupid',
        groupname VARCHAR(50) 'groupname',
        grouptext VARCHAR(100) 'grouptext'
    )
) AS source(groupid, groupname, grouptext)

ON
    t.groupid = source.groupid AND
    t.groupname = source.groupname

WHEN MATCHED THEN
UPDATE SET
    groupname = source.groupname,
    grouptext = source.grouptext

WHEN NOT MATCHED BY TARGET THEN
    INSERT (groupname, grouptext)
    VALUES (source.groupname, source.grouptext)

WHEN NOT MATCHED BY SOURCE AND
    s.groupname = t.groupname AND
    s.grouptext = t.grouptext
        THEN
            delete
;

Answer 1

MERGE 语句中的

WHEN NOT MATCHED BY SOURCE 子句只接受 TARGET 表中的列。您可以使用子查询，例如:

WHEN NOT MATCHED BY SOURCE AND
    t.groupname IN (SELECT s.groupname FROM table_name where xyz_condition)