• Comments posted to this topic are about the item TSQL

  • I knew my reply was going to be considered wrong before I submitted, but I just didn't want to submit an incorrect reply, even though I second-guessed the author's mind.

    WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE are the three valid subclauses of a MERGE statement. Of these, only WHEN NOT MATCHED BY TARGET has an optional part, so WHEN NOT MATCHED [BY TARGET] is correct as well. (Note that the square brackets are not part of the syntax, but a signifier that the BY TARGET part can be left out).

    However, the BY SOURCE part of WHEN MATCHED BY SOURCE is not optional. Which makes sense, of course, for without the BY SOURCE part it couldn't be distinguished from a WHEN NOT MATCEHD BY TARGET clause. Therefor, WHEN NOT MATCHED [BY SOURCE] is not correct, even though WHEN NOT MATCHED BY SOURCE (without the brackets) is.

    Suggestion to Steve: Change the answers. Remove the square brackets in all of the answer options, and you're done. (Or, to make it even more interesting, add in an extra option for WHEN NOT MATCHED without BY xxx clause - that is of course allowed as well, since the BY TARGET part of WHEN NOT BATCHED BY TARGET is optional).

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hi,

    Thanks for the reply. Noted your point. Will take care of this in future.



  • Yes you never know if the question wants totally exact answers (as some do) - or not - as in this case where WHEN NOT MATCHED BY SOURCE is the correct syntax, but the available answer is WHEN NOT MATCHED [BY SOURCE] is accepted as correct.

  • Hugo,

    Thanks and good seeing you last week.

    Didn't think about those parts being optional when I reviewed this. I kind of glanced at it and took those to be valid options.

    The answers have been changed.

  • Nice point Hugo. Very good explanation...:)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply