So far, I've figured out 4 possible ways to perform upsert-like
functionality:

1. try insert, on exception update
2. try update, on exception insert
3. delete, then insert
4. merge with DUAL

It seems so far the nicest solution is #4.

Here's some questions:

- Do the other methods have any features which might recommend them?

- Are there any methods that might be portable to other databases?

- Are there any methods I might be overlooking?

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

<mh (AT) pixar (DOT) com> wrote

#3 is terrible. You are generating a lot more redo etc. than you need to.
Just use the merge statement. I don't see what dual has to do with it. You
should be able to do it in one statement.
Jim

<mh (AT) pixar (DOT) com> wrote

#3 is terrible. You are generating a lot more redo etc. than you need to.
Just use the merge statement. I don't see what dual has to do with it. You
should be able to do it in one statement.
Jim

<mh (AT) pixar (DOT) com> wrote

#3 is terrible. You are generating a lot more redo etc. than you need to.
Just use the merge statement. I don't see what dual has to do with it. You
should be able to do it in one statement.
Jim

<mh (AT) pixar (DOT) com> wrote

#3 is terrible. You are generating a lot more redo etc. than you need to.
Just use the merge statement. I don't see what dual has to do with it. You
should be able to do it in one statement.
Jim

On Oct 26, 4:10*am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:
The issue here is to perform a MERGE using only one table, not two,
hence the use of DUAL. Out of all of the listed possibilities the
actual MERGE, using DUAL, is your best bet:

merge into employee e
using (select 20 as id from dual) i on (e.id = i.id)
when matched then
update
set salary = salary + 10000
when not matched then
insert
values('BLORPO',20,12000)
/

You'll not find anything useful in this regard that is portable; then,
why do you need it to be? You're better served by writing to the dbms
in use, not attempting to generate 'universal' code which likely won't
scale nor will it perform.


David Fitzjarrell

On Oct 26, 4:10*am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:
The issue here is to perform a MERGE using only one table, not two,
hence the use of DUAL. Out of all of the listed possibilities the
actual MERGE, using DUAL, is your best bet:

merge into employee e
using (select 20 as id from dual) i on (e.id = i.id)
when matched then
update
set salary = salary + 10000
when not matched then
insert
values('BLORPO',20,12000)
/

You'll not find anything useful in this regard that is portable; then,
why do you need it to be? You're better served by writing to the dbms
in use, not attempting to generate 'universal' code which likely won't
scale nor will it perform.


David Fitzjarrell

On Oct 26, 4:10*am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:
The issue here is to perform a MERGE using only one table, not two,
hence the use of DUAL. Out of all of the listed possibilities the
actual MERGE, using DUAL, is your best bet:

merge into employee e
using (select 20 as id from dual) i on (e.id = i.id)
when matched then
update
set salary = salary + 10000
when not matched then
insert
values('BLORPO',20,12000)
/

You'll not find anything useful in this regard that is portable; then,
why do you need it to be? You're better served by writing to the dbms
in use, not attempting to generate 'universal' code which likely won't
scale nor will it perform.


David Fitzjarrell

On Oct 26, 4:10*am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:
The issue here is to perform a MERGE using only one table, not two,
hence the use of DUAL. Out of all of the listed possibilities the
actual MERGE, using DUAL, is your best bet:

merge into employee e
using (select 20 as id from dual) i on (e.id = i.id)
when matched then
update
set salary = salary + 10000
when not matched then
insert
values('BLORPO',20,12000)
/

You'll not find anything useful in this regard that is portable; then,
why do you need it to be? You're better served by writing to the dbms
in use, not attempting to generate 'universal' code which likely won't
scale nor will it perform.


David Fitzjarrell

mh (AT) pixar (DOT) com wrote:
examples of why you should use merge

--

Regards,
Frank van Bortel