Poor error message with MERGE when source/target appear in impossible places
If I write a MERGE statement and refer to source in WHEN SOURCE NOT MATCHED
or to the target in WHEN TARGET NOT MATCHED, I get an error message. It's a
good thing in so far that it saves me from writing silly queries. Unfortunately,
the error message is the generic 'The multi-part identifier "%S" could not be bound.',
which is almost more confusing than helpful, as it makes me think that I have a
spelling error rather than this specific error.
Upvotes: 1<=-=Apr 26 2008 8:21PM=-=>
I think the error message is the correct one, By analogy, suppose you accidentally wrote
from Orders as o
join “Order Details” as d
on o.OrderID = d.OrderID
) as ord
You get the same “multi-part identifier could not be bound” error. You would get this error if you wrote SELECT oops.OrderID, too.
Erland seems to be suggesting a different error message for o.OrderID, like “Cannot refer to a table name or alias outside a derived table where it is defined”. While that could be useful, the syntax checker shouldn’t have to consider whether or not the identifier that can’t be bound happened to contain an alias [o] that appears somewhere else in the same query.
The RTM syntax for MERGE will probably reduce this kind of confusion. The syntax for the two not-matched clauses will be
WHEN NOT MATCHED [BY TARGET] — row exists in source but not in target
WHEN NOT MATCHED BY SOURCE — row exists in target but not in source
Under WHEN NOT MATCHED BY SOURCE, people will probably be less likely to mistakenly refer to the SOURCE alias.
SK<=-=Apr 28 2008 4:09PM=-=>
Thank you for the feedback. We’ve had several comments about this, and agree that this is an area where confusion is quite likely. To help developers quickly find and correct these mistakes, 2 new error messages were recently introduced:
(5333) The identifier ‘%.*ls’ cannot be bound. Only source columns are allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
(5334) The identifier ‘%.*ls’ cannot be bound. Only target columns are allowed in the ‘WHEN SOURCE NOT MATCHED’ clause of a MERGE statement.
While it’s not possible to give suggestions like this when a column name can’t be bound in the general case, this is a case where we can give specific feedback that some columns have been hidden by the binding algorithms. These should be present in the next release.
SQL Server Engine Development
Great to see that you already have addressed this. However, you error messages use the old CTP6 syntax for MERGE. It should use the new, to appear in RC0, I believe. See
You’re correct. I only looked at the change that introduced the error messages and should have looked at the latest version. The syntax change was made after these error messages were introduced and the error messages were updated along with the syntax. The current version of 5334 is this:
The identifier ‘%.*ls’ cannot be bound. Only target columns are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement.
SQL Server Engine Development
The error message is the same in SQL 2008 RTM, why I have reopened this item.<=-=Feb 4 2009 5:40PM=-=>
Thanks for the feedback. We will look to address this issue in a future release of SQL Server as we do not change error messages in service pack releases.
Thank you and best regards,