--- In Firebird-Architect@yahoogroups.com, "Martijn Tonies"
<m.tonies@...> wrote:

>
>
> > A> Arno Brinkman wrote:
> > >> When showing the "INSERT OR UPDATE" it was clear for both that
> > >> it will perform first INSERT else UPDATE, because
> > >> internally it first performs UPDATE and then INSERT when needed
> > >> we suggest to use "UPDATE OR INSERT".
> > >>
> > A> Yes, if it is to be changed, the better name is "UPDATE OR INSERT".
> >
> > Not necessarily. It could be interpreted as "INSERT OR, if the record
> > already exists, UPDATE it". It is actually what I think when reading
> > "INSERT OR UPDATE". I don't think at the temporal sequence of events.
>
> Lads, don't overdo it, or we might end up with:
>
> INSERT INTO IF EXISTS UPDATE ....
>
> :-)

Maybe

INSERT INTO ......
UNLESS IT IS ALREADY THERE IN WHICH CASE [SOME OTHER DML]

or perhaps

UPDATE .....
BUT IF NO RECORDS ARE UPDATED THEN [SOME OTHER DML]

Then there shall be no confusion ;p

Seriously though. The although the replace keyword implementation in
MySQL is arguably done the wrong way, it is out there and working as
documented. To come up with a different interpretation (even if the
new implementation seems better) will most likely cause confusion for
those coming from a MySQL background.

I prefer 'insert or update', or 'update or insert' myself because
these commands are self explanatory in the actions they take. If we go
back to the heart of the problem, I have an employee record (1, Adam).
Within pure SQL (prior to Merge), there is no single command I can
send through to say I want to make sure there is a record as
specified. If not, insert it, if so, do an update (based on some key
matching). So we are left with a few options. Do an insert and catch
an exception, or do an update and check ROW_COUNT to see if an insert
is required. This is multiple round trips to the database (if
ROW_COUNT even works outside PSQL, not sure).

I do not know whether it really matters which happens first (the
insert or the update). I suppose if you have triggers that perform
actions outside transaction control (write to external table etc),
then it may be significant, but all I want is a single 'operation' to
achieve my goal of getting my employee record into the table with a
single trip, so it does not matter to me which occurs first.

Perhaps both 'insert or update' and 'update or insert' could be both
implemented, and the one you choose would determine which triggers are
fired first.

Adam