title: Merge when not matched by source and
body: <p>I have a table 'groups' which has some data in it:</p>
  
  <pre><code>groupid     groupname       grouptext
  1           Cars            Toyota
  2           Cars            BMW
  3           Cars            Renault
  4           Bikes           BMW
  5           Bikes           Yamaha
  </code></pre>
  
  <p>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</p>
  
  <pre><code>groupid     groupname       grouptext
  1           Cars            Audi
  4           Bikes           BMW
  5           Bikes           Yamaha
  6           Singers         Lady Gaga
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>My question is, how can I achieve the desired result using the following code:</p>
  
  <pre><code>DECLARE @UpdateXml Xml = N'&lt;groups&gt;
      &lt;group&gt;
          &lt;groupid&gt;1&lt;/groupid&gt;
          &lt;groupname&gt;Cars&lt;/groupname&gt;
          &lt;grouptext&gt;Audi&lt;/grouptext&gt;
      &lt;/group&gt;
      &lt;group&gt;
          &lt;groupid&gt;0&lt;/groupid&gt;
          &lt;groupname&gt;Singers&lt;/groupname&gt;
          &lt;grouptext&gt;Lady Gaga&lt;/grouptext&gt;
      &lt;/group&gt;
  &lt;/groups&gt;';
  
  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
  ;
  </code></pre>