Error formatting MERGE statement in WHEN NOT MATCHED BY

opc.threeopc.three Posts: 17 New member
edited January 7, 2016 6:42AM in SQL Prompt 
WHEN NOT MATCHED BY SOURCE AND SomeID = @SomeID THEN DELETE
The above code inside a MERGE statement is giving SQL Prompt problems causing it to show a Syntax error underlining column SomeID.
· Share on Twitter

Comments

  • Anu DAnu D Posts: 876 Silver 3
    Hi,

    Thanks for your post.

    I was not able to replicate the issue so it will be really helpful if you email me the complete query?

    The red underline is the native SSMS feature and not SQL Prompt's. The setting is here: in SSMS navigate to Tools --> Options --> Text Editor --> Transact SQL --> Intellisense do you have the option "Underline errors" checked here? If yes this is what is triggering in your case.

    Also if you get an error from SQL prompt please let me know the detailed version as well? (In SSMS navigate to SQL prompt --> Help --> About SQL Prompt)

    I will email you so you can reply me with the complete query that fails.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
    · Share on Twitter
  • opc.threeopc.three Posts: 17 New member
    I cannot post the entire query however I did revisit this and see there is additional info when I hover over the red squiggly underline, it says:
    The column SomeID could not be resolved as a column with that name exists in multiple tables

    The use of the English language in the error message is dreadful however I made sure to type it in exactly as it is shown, i.e. that is the exact error message shown.

    By me adding a "target."-qualification to the column SomeID in my original code the error goes away however this should not be necessary because SQL Server does not require it, i.e. it is unambiguous so SQL Prompt should not complain. By definition "WHEN NOT MATCHED BY SOURCE" any additional predicates can only be referring to the target otherwise the predicate would be trivial.
    WHEN NOT MATCHED BY SOURCE AND SomeID = @SomeID THEN DELETE

    I have overcome the issue but I still think SQL Prompt should relax on this validation to match SQL Server.
    · Share on Twitter
  • Ali DAli D Posts: 56 New member
    Hi opc.three

    Sorry for the confusion, we think we’ve now been able to reproduce this issue and we’ll look into improving this.

    Thanks
    Ali
    · Share on Twitter
  • Ali DAli D Posts: 56 New member
    Hi opc.three

    This issue should now be fixed in the latest beta (7.1.0.234): https://forums.red-gate.com/viewtopic.php?f=169&t=78967

    Please let us know if you have any issues with this build.

    Thanks
    Ali
    · Share on Twitter
Sign In or Register to comment.