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.

share
    
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 Jan 22 '13 at 23:33
add comment

1 Answer

up vote 10 down vote accepted

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

share
    
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. –  Erwin Brandstetter Jan 27 '13 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. –  Martin Smith Jan 27 '13 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 Jan 29 '13 at 14:14
2  
Similar non-Update/Insert functionality exists in Oracle for Merge. –  Leigh Riffel Jan 29 '13 at 14:27
2  
UPSERT could be considered a subset of MERGE, but not synonymous. Perhaps their wiki entries should reflect this. –  Leigh Riffel Jan 29 '13 at 14:27
1  
I updated the tag wiki for UPSERT with results from this discussion. –  Erwin Brandstetter Feb 3 '13 at 0:29
add comment

You must log in to answer this question.

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