Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Expand / Collapse
Author
Message
Posted Tuesday, November 25, 2008 9:55 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:11 AM
Points: 1,569, Visits: 56
Comments posted to this topic are about the item TSQL
Post #608838
Posted Wednesday, November 26, 2008 12:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:07 PM
Points: 6,002, Visits: 8,266
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #608884
Posted Wednesday, November 26, 2008 3:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:11 AM
Points: 1,569, Visits: 56
Hi,

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

Regards,

Sudhir
Post #608924
Posted Wednesday, November 26, 2008 4:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430

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.




Post #608963
Posted Wednesday, November 26, 2008 8:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:38 AM
Points: 33,267, Visits: 15,436
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #609155
Posted Thursday, November 27, 2008 8:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,368, Visits: 1,391
Nice point Hugo. Very good explanation...:)


Post #609828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse