0

In MySQL, we can perform an upsert using the ON DUPLICATE KEY clause without knowing or caring what the primary key is. I would further wager, 99% of all MERGE use cases in SQL Server have the ON clause be ON (source.primary_key = target.primary_key). So my question is, Is there a way to have the ON clause be implicitly the primary without specifying column(s) of the primary key?

Dale K
  • 25,246
  • 15
  • 42
  • 71
kevinc
  • 605
  • 5
  • 14
  • 7
    No............. – Dale K Jul 16 '23 at 21:54
  • 2
    The [official documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16) is always the best place to get such answers – Dale K Jul 16 '23 at 22:02
  • 4
    _"Is there a way to have the ON clause be implicitly the primary without specifying column(s) of the primary key?"_ - "natural" JOINs using implicit equijoins on implicit columns sounds like a good idea provided you don't think too hard about it - then you realise it's a terrible idea. – Dai Jul 16 '23 at 22:09
  • @dai When you argue against making the 99% use case inconvenient.. I think the word is pedantic. – kevinc Jul 16 '23 at 22:21
  • 5
    SQL is confusing enough for most people without adding implicit behaviour... best to be explicit... doesn't really have a downside. – Dale K Jul 16 '23 at 22:27
  • Seems like a duplicate https://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update – Dale K Jul 16 '23 at 22:30
  • 1
    @kevinc On the contrary: I'm arguing what you're proposing won't make the 99% use-case inconvenient: I'm arguging what you're proposing _will cause users to lose data probably ~20% of the time in the 99% use-case_. Things that are potentially dangerous are inconvenient for a reason. (After all, [MySQL prohibits `UPDATE` and `DELETE` without a PK in the `WHERE`](https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) clause unless you fiddle with settings, arguably that's even more inconvenient than what we're discussing: – Dai Jul 16 '23 at 22:36
  • 1
    I suppose you could generate the merge dynamically, but yeah sounds like a really bad idea. – Charlieface Jul 17 '23 at 00:07
  • 'In MySQL, we can perform an upsert using the ON DUPLICATE KEY clause without knowing or caring what the primary key' - not quite ''If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, ' https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html - which makes sense if the unique key consists of more than 1 column and differs in all regards from the primary key. put another way mysql will check all unique indexes (including primary) – P.Salmon Jul 17 '23 at 09:08
  • Think of the poor microsoft developers, MERGE is buggy enough as it is! – siggemannen Jul 17 '23 at 09:15

0 Answers0