Subject Re: [Firebird-Architect] REPLACE, again
Author Alex Peshkov
Leyne, Sean wrote:
> Adriano,
>>Last time we discussed REPLACE if fb-devel, one user suggested another
>>syntax that's looking better for me:
>> REPLACE INTO <table> [(<field_list>)] VALUES (<value_list>)
>>(<field_list>) [RETURNING <value_list>]
>>This syntax make the statement not dependent on a PK and hence it
> could
>>update more than one row.
>>It will be usable with views too.
>>Not depending on a PK, it could be implemented in DSQL with existing
>>verbs, equivalent to:
>> update ....
>> if (row_count = 0) then
>> insert ...
>>As we don't support RETURNING more than one row yet, the generated RSE
>>of the update will be a singleton (only when RETURNING is used?).
> On one hand you talk about updating multiple rows, but you then say the
> statement could only RETURNING a single row!
> It looks like elegant syntax looking for an excuse to exist -- I don't
> see what problem this statement is going to be usefull in solving.

If we plan to support MATCHING, we can't support RETURNING at the same
time in 2.1. A compromise may be to support MATCHING and RETURNING not
at the same time, i.e. one may type:
REPLACE INTO <table> [(<field_list>)] VALUES (<value_list>) MATCHING
REPLACE INTO <table> [(<field_list>)] VALUES (<value_list>) [RETURNING

In the second case (no MATCHING clause) PK is used, making update of
only single row possible, therefore we can have singleton RETURNING.
This form is also useful because most of REPLACE in well-formed database
should use PK, and this syntax saves some typing and therefore helps
end-user avoid some errors.

In the future, when we will learn to support RETURNING multiple rows,
form without MATCHING (using PK), will remain useful anyway.