×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Update esle insert ?insert else update ?

Update esle insert ?insert else update ?

Update esle insert ?insert else update ?

(OP)
hello,

In some ETL, i've the option :
Update else insert.
Insert else update.
I've some difficult to understand the differences between this 2 options.
In fact, with the first or the second option,my date are update and insert.

Somebody can explain me ?
Have exemples ?

Thanks

RE: Update esle insert ?insert else update ?

What is your database?  In SQL server there is a technique called upsert that does what you speak of.  It works like this:

CODE

If exists (select * from myTableDest where ID = @param)

update a
set a.ColumnA = b.something
, a.Column1 = b.somethingelse
from myTableDest a
inner join myTableSource b
on a.ID = b.ID
where a.ID = @param

else

insert into myTableDest
select @param, ColumnA, Column1
from myTableSource

Where @param is the unique identifier of the record to be inserted/updated.  I am not sure of how to do this with anything but SQL server though.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

RE: Update esle insert ?insert else update ?

(OP)
my database is Oracle (one of the best database in the world)

RE: Update esle insert ?insert else update ?

Donny -

The best database in the world is one that is properly designed.  No database software (even if it is the VERY best in the world) will make up for poor design.

I can't imagine that Oracle does not have the capability to do an 'upsert', but I suggest you try the Oracle forum.  Here is the forum for the most recent version:

forum1177

Good luck,

Alex

Ignorance of certain subjects is a great part of wisdom

RE: Update esle insert ?insert else update ?

Agree, check out UPSERT statement. The difference between Insert else Update
and
Update else Insert
depends on which is more likely. Better performance is achieved if the first option occurs more frequently than the second.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: Update esle insert ?insert else update ?

(OP)
ok
in fact,
If i do more insert than update, i use insert else update and if i do more update than insert i use update else insert;
and it can improve consequently the load performances
it's that ?

RE: Update esle insert ?insert else update ?

In Oracle, the conditional update or insert structure is the Merge Statement

RE: Update esle insert ?insert else update ?

Yes, that will help performance. I stand corrected - the Oracle command is MERGE. Upsert is a conceptual term.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: Update esle insert ?insert else update ?

John, I wasn't meaning to correct you, I realised that you meant UPSERT as a concept. I just noticed that the Oracle structure hadn't actually been named at that point so I thought I'd stick my big nose in :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close