Does this fill the same role as "Insert - On Conflict Do Update," but it doesn't require unique indexes?
upsert is built on top of insert while merge allows you specify multiple conditions and includes the ability to perform a delete.
i.e if you have a blog post with a list of tags, you could say:
- if submitted tag is not found > insert it
- if submitted tag is found > do nothing
- if tag exists that wasn't submitted > delete it
It can be quite powerful but it can get difficult to read also. I just hate using it in SQL Server because every time someone adds it, a year later its the code causing deadlocks.
Now I need to dig deeper on all the concerns brought up there around non-atomicity and table level locking!
Seems like it could be useful specifically for bulk uploads.
(With great power comes great responsibility; as others point out MERGE in SQL Server can get you big table deadlocks if you aren't careful, but on the flipside in my experience there were a lot of very hairy stored procedures I was able to rewrite as single much easier to read MERGE statements that had far better query plans and were much faster overall.)