5

I created a tag (7x) today and wrote a somewhat useful (I hope) tag wiki.

Later I found that there is already a tag (15x, no wiki). Would you consider the two synonymous? I can't mark them as such, my score for is too low.

1
  • I think that there is a discussion amongst the folks in the field as to if this is "reasonable syntax" for most devs, because the underlying mechanics can be quite distinct. Let's not be rash, but I do hope to see a discussion on this.
    – jcolebrand Mod
    Commented Jan 22, 2013 at 23:33

1 Answer 1

10

No they are not synonymous.

An UPSERT can be implemented without using the MERGE keyword and conversely UPSERT is just one possible use case for MERGE (when it has both a WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED [ BY TARGET ] THEN INSERT ...)

At least in SQL Server it is not required that MERGE have both (or indeed either) of these clauses.

The following use of MERGE neither updates nor inserts.

CREATE TABLE T(A INT)

INSERT INTO T VALUES (1),(2),(3);

MERGE INTO T
USING (VALUES (1),
              (2)) S(A)
ON T.A = S.A
WHEN NOT MATCHED BY SOURCE THEN
  DELETE; 

Other (possibly SQL Server centric) use cases for Merge that have nothing to do with Upserts is inserting DEFAULT VALUES for multiple rows or Using merge..output to get mapping between source.id and target.id

6
  • 1
    So there are obviously subtly differences. Should we have two separate tags? It would seem they fit into the same class of questions, especially since those who seek advice might not be aware of the details. Commented Jan 27, 2013 at 20:26
  • @ErwinBrandstetter - I think they have to be separate tags rather than synonymized or people searching the upsert tag could end up with a whole load of irrelevant results that are nothing to do with upserts but are to do with alternate uses of MERGE. Commented Jan 27, 2013 at 20:36
  • 5
    +1 for separate tags. There is overlap between upsert and merge, but easily enough difference IMHO to prevent synonymization (!)
    – Paul White Mod
    Commented Jan 29, 2013 at 14:14
  • 2
    Similar non-Update/Insert functionality exists in Oracle for Merge. Commented Jan 29, 2013 at 14:27
  • 2
    UPSERT could be considered a subset of MERGE, but not synonymous. Perhaps their wiki entries should reflect this. Commented Jan 29, 2013 at 14:27
  • 1
    I updated the tag wiki for UPSERT with results from this discussion. Commented Feb 3, 2013 at 0:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .