Re: MERGE vs REPLACE

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
>