In a MERGE statement, a variable cannot be set to a column and expression in the same assignment in the SET clause of an UPDATE action. Assignments of the form 'SET @variable = column = expression' are not valid in the SET clause of an UPDATE action in a MERGE statement. Modify the SET clause to only specify assignments of the form 'SET @variable = column' or 'SET @variable = expression'.

Suppose we have created two tables and inserted some records by using following sql query:

CREATE TABLE tblSource(

    ID INT IDENTITY PRIMARY KEY,

    vcData VARCHAR(500)

)

INSERT INTO tblSource VALUES('PC'),('Laptop'),('Mobile')

CREATE TABLE tblTarget(

    ID INT IDENTITY PRIMARY KEY,

    vcData VARCHAR(500)

)

INSERT INTO tblTarget VALUES('Laptop')

Now if we will execute following merger query:

DECLARE @NewValue AS VARCHAR(50)

MERGE tblTarget

USING tblSource

ON tblTarget.ID =  tblSource.ID

WHEN MATCHED THEN

    UPDATE SET @NewValue = vcData = tblSource.vcData;

SELECT @NewValue 

We will get error message:

In a MERGE statement, a variable cannot be set to a column and expression in the same assignment in the SET clause of an UPDATE action. Assignments of the form 'SET @variable = column = expression' are not valid in the SET clause of an UPDATE action in a MERGE statement. Modify the SET clause to only specify assignments of the form 'SET @variable = column' or 'SET @variable = expression'.

Cause: It is due to syntax error at "UPDATE SET @NewValue = vcData = tblSource.vcData "

Solution:

We can resolve this error by writing above statement following ways:

DECLARE @NewValue AS VARCHAR(50)

MERGE tblTarget

USING tblSource

ON tblTarget.ID =  tblSource.ID

WHEN MATCHED THEN

    UPDATE SET

         @NewValue = tblSource.vcData,

         vcData = @NewValue;

SELECT @NewValue   

Or

MERGE tblTarget

USING tblSource

ON tblTarget.ID =  tblSource.ID

WHEN MATCHED THEN

    UPDATE SET vcData = tblSource.vcData

OUTPUT DELETED.vcData;