> you want to insert a new record but if it already exists, to update

the existing row. As such, the insert must be performed before the
update.

Why must the insert be first? For tables that have before insert
triggers for the PK I perform the update first so that a key isn't
consumed.

> you then say the statement could only RETURNING a single row!

The syntax shows [RETURNING <value_list>] and beneath that he says "we
don't support RETURNING more than one row YET" (my emphasis). Is a
result set for RETURNING anywhere on the roadmap?

-----Original Message-----
From: Firebird-Architect@yahoogroups.com
[mailto:Firebird-Architect@yahoogroups.com] On Behalf Of Leyne, Sean
Sent: Tuesday, August 15, 2006 5:10 PM
To: Firebird-Architect@yahoogroups.com
Subject: RE: [Firebird-Architect] REPLACE, again

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>)

MATCHING

> (<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

BLR

> 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.


I thought the problem that REPLACE was intended to solve, is where you
want to insert a new record but if it already exists, to update the
existing row. As such, the insert must be performed before the update.


Sean



Yahoo! Groups Links