Mar 18, 2014

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;

No comments:

Post a Comment