0

I am using below merge and getting "The multi-part identifier "MasterSource.monthvrip" could not be bound" error. Checked a lot in internet but no luck . Looks like using some defined keyword but not able to figure out. Any suggestion will be helpful.

USE TestDatabase

MERGE [dbo].[cost1] AS target   
USING (     
  SELECT [monthvrip]
  FROM [dbo].[cost]
) AS MasterSource
ON (MasterSource.[monthvrip] = target.[monthvrip])

WHEN MATCHED AND (MasterSource.[monthvrip] <> target.[monthvrip])
THEN UPDATE 
  SET MasterSource.[monthvrip] = target.[monthvrip]

WHEN NOT MATCHED BY target THEN 
INSERT 
  ([monthvrip])
VALUES 
  (MasterSource.[monthvrip])

OUTPUT
$action,
inserted.*,
deleted.*;

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "MasterSource.monthvrip" could not be bound.

Andrew
  • 183
  • 2
  • 14
  • Are you sure this is the right syntax? [Documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15). Why not remove the "derived table" MasterSource since you're not doing any filtering in it, just selecting one column. In the end, just go with `MERGE cost1 as target USING cost as masterSource on masterSource.monthvrip = target.monthvrip` – Radu Gheorghiu Jan 20 '20 at 09:04
  • Thanks. I have lot of columns in table and pasted here 1 so that code can be scanned easily. – Andrew Jan 20 '20 at 09:09
  • Please consult the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15), you cannot use an alias against the column to update. So use `SET [monthvrip] = ` – Dale K Jan 20 '20 at 09:27

1 Answers1

3

You need to change

THEN UPDATE 
  SET MasterSource.[monthvrip] = target.[monthvrip]

to

THEN UPDATE 
  SET target.[monthvrip] = MasterSource.[monthvrip]

The way you have it at the moment, you're trying to update the source of the merge, rather than the target.

Ethan1701
  • 193
  • 1
  • 10