# The identifier ‘source column name’ 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.

Problem Statement:

When you want to have a condition in a MERGE with source and target for WHEN NOT MATCHED clause, you may likely to get an error message as below, if you put the condition directly to the MERGE statement.

The identifier ‘source column name’ 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.

Lets quickly check an example:

``````
Drop table T1,T2
create Table T1(Col1 int, Col2 int)
Insert into T1 Values(1,1),(1,2),(1,3),(1,4),(3,1)--,(2,1)

create Table T2(Col1 int, Col2 int)
Insert into T2 Values(1,1),(1,2),(1,3),(1,5),(2,1)

Select * From T1

MERGE T1 TG
USING T2 SR
ON TG.Col1= SR.Col1 and TG.Col2 = SR.Col2
WHEN MATCHED THEN
UPDATE
SET TG.Col2 = SR.Col2
WHEN NOT MATCHED BY TARGET THEN
INSERT  (Col1, Col2)
VALUES (Col1, Col2)
WHEN NOT MATCHED BY SOURCE AND TG.Col1 = SR.COl1 THEN
DELETE;

Select * From T1
``````

Solution:

Here is a simple way of solving the mentioned issue.

``````
Drop table T1,T2
create Table T1(Col1 int, Col2 int)
Insert into T1 Values(1,1),(1,2),(1,3),(1,4),(3,1)--,(2,1)

create Table T2(Col1 int, Col2 int)
Insert into T2 Values(1,1),(1,2),(1,3),(1,5),(2,1)

Select * From T1

MERGE T1 TG
USING T2 SR
ON TG.Col1= SR.Col1 and TG.Col2 = SR.Col2
WHEN MATCHED THEN
UPDATE
SET TG.Col2 = SR.Col2
WHEN NOT MATCHED BY TARGET THEN
INSERT  (Col1, Col2)
VALUES (Col1, Col2)
WHEN NOT MATCHED BY SOURCE AND TG.Col1 in (Select Col1 From T2) THEN
DELETE;

Select * From T1
``````