Re: MERGE vs REPLACE

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