Re: MERGE Support (SQL2003)

Stephen Frost <sfrost(at)snowman(dot)net> writes:

> c)
> merge into T1 USING VALUES (1,2,4);

I'm not happy with the implied use of the primary key. a) some tables can have
two effective primary keys, even if only one is explicitly declared as such.
and b) some update/inserts are done with where clauses that aren't primary
keys at all. and c) you might want to update any record for a date within the
last week but if you insert then insert with the current date.

I see this as vaguely analogous to the NATURAL JOIN/ON/USING where I feel
using NATURAL JOIN to work automatically is somewhat risky and hides too much
of whats happening. I would think the key columns should always be explicitly
named in permanent code.

Similarly vaguely analogous to the ON/USING distinction I would think you
would want a form of this where the key columns simply named. And one where
the where clauses could be enumerated.

So I would suggest doing something like this which is basically exactly
equivalent to an INSERT statement except with an added ON/USING clause exactly
like the syntax in SELECT.

MERGE INTO t1 USING (col1,col2) VALUES (1,2,4)

Or explicitly naming columns (which I argue should be done on inserts):

MERGE INTO t1 USING (col1,col2) (col1,col2,col3) VALUES (1,2,4)

and

MERGE INTO t2 ON (col1='1',col2='2' OR col2='3')
(col1,col2,col3)
VALUES
(1,2,4)

--
greg