Geoff Worboys wrote:

>> /* REPLACE statement */
>
>> replace
>> : REPLACE INTO simple_table_name ins_column_parens_opt
>> VALUES '(' value_list ')'
>> replace_matching_opt
>> returning_clause
>> ;
>
> Could you try something like this:
>
> INSERT INTO simple_table_name ins_column_parens_opt
> VALUES '(' value_list ')'
> [REPLACE WHERE ins_column_parens]
> returning_clause
> ;

It does seem somewhat stupid that the SQL2003 standard has no means of
handling what is quite a standard problem. As people have pointed out,
the MERGE command is lacking in a single line mode, and both INSERT and
UPDATE are mutually exclusive. So we have little option in adding our
own version.
Since we are fairly closely matched to Oracle is anything provided there
for this situation?
Having now seen some statements about 'REPLACE' I think that we could
make an argument that the MySQL process is correct in that your new
record SHOULD replace the existing one and so perhaps the discussion
there would be worth exploring as a DIFFERENT statement to the one
currently proposed - with Jim's input on why the MySQL version breaks down.
As we have already introduced the concept of CREATE OR ALTER I think
that it is a logical step to add INSERT OR UPDATE and simply combine
functions that are already in the standard, and follow the standards
rules based on the existence or not of a record.
REPLACE would then logically be DELETE AND INSERT and again simply
combine two existing functions?

--
Lester Caine - G8HFL
-----------------------------
L.S.Caine Electronic Services - http://home.lsces.co.uk
Model Engineers Digital Workshop -
http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php