Re: MERGE vs REPLACE

Just to summarize, MySQL REPLACE is INSERT or DELETE/INSERT, while they
have a SET clauses that allows UPDATE, and INSERT has a ON DUPLICATE KEY
UPDATE clause too.

I think the INSERT ... ON DUPLICATE KEY is undesirable because this
functionality should have a new keyword in the first position, e.g
MERGE, and I think the REPLACE is out because most people feel that the
DELETE/INSERT functionality is near-useless if we can give users the
INSERT/UPDATE functionality of MERGE.

I think even if we have to restrict MERGE to requiring a unique index,
it is better to go that way than to drag REPLACE into our syntax. MERGE
can be extended over time, while REPLACE has a non-optimal initial
behavior. The idea that MERGE can use a constant list (not requiring a
second table) makes it a valid replacement for REPLACE, and other
database support for MERGE reinforces this.

---------------------------------------------------------------------------

pmagnoli(at)systemevolution(dot)it wrote:
> I think you translated it correctly, MySQL has another way of specifying this
> which is "INSERT ... ON DUPLICATE KEY UPDATE ..."
> (http://dev.mysql.com/doc/refman/5.0/en/insert.html)
> Regards
>
> Paolo
>
> Jochem van Dieten <jochemd(at)gmail(dot)com> ha scritto
>
> > On 11/13/05, Petr Jelinek wrote:
> > >
> > > I am really not db expert and I don't have copy of sql standard but you
> > > don't need to use 2 tables I think - USING part can also be subquery
> > > (some SELECT) and if I am right then you could simulate what REPLACE
> > > does because in PostgreSQL you are not forced to specify FROM clause in
> > > SELECT. So you could in theory do
> > > MERGE INTO tablename USING (SELECT 1 AS myid) ON (tablename.id = myid)
> ...
>
> > I think the MySQL statement:
> > REPLACE INTO table (pk, col1, col2, col3) VALUES (2, '0000-00-00', NULL,
> 3)
>
> > would translate into the following MERGE statement:
> > MERGE INTO table target
> > USING (2 as pknew , NULL as col1new, NULL as col2new, 3 as col3new) source
> > ON target.pknew = source.pk
> > WHEN MATCHED THEN UPDATE SET col1 = col1new, col2 = col2new, col3 =
> col3new
> > WHEN NOT MATCHED THEN INSERT (pk, col1, col2, col3) VALUES (pknew,
> > col1new, col2new, col3new)
>
> > It might not be the most elegant solution, but I don't see why it won't
> work.
>
> > Jochem
>
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
>
> > http://archives.postgresql.org
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073