On Sat, May 3, 2008 at 10:24 AM, Helen Borrie <helebor@...> wrote:

> As UPDATE OR INSERT, yes? I think we lost MERGE after the alpha...or is it
> hiding in there?

Hi Helen,

From what I could understand from a paper written by Vlad, MERGE
cannot be implemented as an UPDATE OR INSERT, because UPDATE OR INSERT
uses a single table whereas MERGE uses a join of two tables.

UPDATE OR INSERT would be equivalent to the following construction:

UPDATE <table>
SET col1 = val1, ... colN = valN
WHERE matchingcolumn1 = val1 AND ... AND matchingcolumnN = valM);
IF (ROWCOUNT = 0) THEN
INSERT INTO <table> [(col1, ..., colN)]
VALUES (val1, ..., valN)

whereas MERGE is handled by the engine like the following complex construction:

FOR SELECT <table>.RDB$DBKEY
FROM <table_or_join> LEFT JOIN <table>
ON <search_condition>
INTO :table_dbkey
DO IF (:table_dbkey IS NULL)
THEN INSERT INTO <table> ....
ELSE UPDATE <table> SET .....
WHERE RDB$DBKEY = :table_dbkey;

As for MERGE's existence in FB 2.1 final, it is still there as I am
currently using it in a system.

Fidel.