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