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. --------------------------------------------------------------------------- [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster