"Adriano dos Santos Fernandes" <adrianosf@...> wrote:

>
> SQL standard has MERGE statement, used to insert a row when a condition
> is false or update when the condition is true.
> MERGE is not very user friendly.
> It's like a INSERT and UPDATE mixed in one statement. You should pass
> the parameters twice.

Correct. I don't like it much either :-) But it provides more flexibility.
Imagine that you need to either increment values for an existing record or
add a new record with the base values.

Could we consider implementation of the SQL-compliant MERGE and then add
REPLACE (or just a more user-friendly syntax of MERGE) as a shortcut for the
simplified semantics? I mean to implement them both using a single BLR verb.

> Syntax of REPLACE can be one of these:
>
> REPLACE INTO table_name [(column [, column ...])] VALUES (value [,
> value ...])
> replace into table (pk, x) values (1, 11);
> replace into table (values (1, 11);
>
> or
>
> REPLACE table_name SET column = value [, column = value ...]
> replace table set pk = 1, x = 11;

MERGE INTO ... VALUES (...) sounds better to me (considering my point
above). But I may live with your second suggestion as well :-)

> REPLACE needs that the table has a PK and that all fields of the PK has
> in the statement.

Of course.

> Could we store a dependency of a SP on a constraint name for this
> condition don't break or we do this verification before execute REPLACE
> (maybe in CMP?)?

IMO, we could store a dependency on a constraint index name. I doubt we'll
ever have primary keys not enforced by indices, this makes no practical
sense to me.

> If the first, I think it can be implemented without new BLR verb
> (although I don't think that will be good). If the second, a new BLR
> verb is required.

I don't see any problem with a new BLR verb, if necessary.


Dmitry