Incorrect syntax when using MERGE statement

Question

When I use the MERGE statement, I get an "invalid syntax" error:

MERGE ResponsibleLawyers AS RL
USING @tempPerson AS TP ON RL.ResponsibleLawyerID = TP.ResponsibleLawyerID

WHEN MATCHED THEN
    UPDATE 
        SET RL.FirstName = TP.FirstName, 
            RL.LastName = TP.LastName, 
            RL.AccountName = TP.AccountName, 
            RL.EmailAddress = TP.EmailAddress, 
            RL.Region = TP.Region, 
            RL.Active = TP.Active 

WHEN NOT MATCHED BY RL THEN
    INSERT (ResponsibleLawyerID, FirstName, LastName, AccountName, EmailAddress, Region, Active) 
    VALUES (TP.ResponsibleLawyerID, TP.FirstName, TP.LastName, TP.AccountName, TP.EmailAddress, TP.Region, TP.Active)

WHEN NOT MATCHED BY SOURCE THEN 
    DELETE 

I get an error "Invalid syntax" beside "RL". I'm using 110 compatibility level on SQL Server 2012. Does anyone know what's wrong?

WHEN NOT MATCHED BY RL THEN

Answer 1

I think WHEN NOT MATCHED BY RL THEN should be WHEN NOT MATCHED BY TARGET THEN. You use SOURCE & TARGET here, not the table alias. The confusion may arise because many examples use SOURCE & TARGET as the table alias.

Reference