Merge when not matched by source and
sql-server, xml
Solution
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)
Problem
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 ; ```