Merge when not matched by source and

Question

I have a table 'groups' which has some data in it:

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

After that, I am the following SQL statements, in which I am trying to update the groups table using MERGE statement. What I want to do is to update the groups table with the data in XML. But I also want to delete all the entries in the table whose groupname is present in the XML. So for example after execution my table should look like this

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

Since, there was groupname, and cars was in there, all the cars should be removed from the table. Now, when I use WHEN NOT MATCHED BY SOURCE AND clause I am getting this error:

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.

My question is, how can I achieve the desired result using the following code:

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

WHEN NOT MATCHED BY SOURCE clause in a MERGE statement only accepts columns from the TARGET table. You can use a SubQuery, like:

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