Re: MERGE vs REPLACE

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> I disagree in that REPLACE is advertised as a solution for the INSERT else
> UPDATE problem, but has a different behavior than a true INSERT else UPDATE
> would produce. Maybe that's a problem with the implementation, or maybe
> it's a problem in the advertisment, but there is certainly a discrepency
> there.

Yeah. REPLACE fails to solve common examples like a web hit counter
("if key doesn't exist, insert row with count 1; if it does exist,
add 1 to the current count").

IIRC, SQL's MERGE deals with this by offering two quite separate
specifications of what to do when there is or isn't already a matching
row.

I don't necessarily feel that we have to slavishly duplicate what MySQL
offers. I do think that it's reasonable to restrict the functionality
to updating/replacing a row with matching primary key --- that gets us
out of the problem of needing a full predicate-locking mechanism, while
still covering most all of the practical use-cases that I can see.

It'd be useful to look at what comparable functionality is offered by
other DBs besides MySQL. Anyone know what DB2 or Oracle have in this
area?

regards, tom lane