Oracle UPSERT
Moderators: chulett, rschirm, roy
Oracle UPSERT
Hi Experts,
Already a select statement is there for Insert Query of Target Oracle stage whose mode is UPSERT.What happens if we write the same query with select statement for Update Query?
I observed this in one of my existed jobs.Is there any specific meaning for it?
Thanks in Advance,
Srini
Already a select statement is there for Insert Query of Target Oracle stage whose mode is UPSERT.What happens if we write the same query with select statement for Update Query?
I observed this in one of my existed jobs.Is there any specific meaning for it?
Thanks in Advance,
Srini
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Quite. I also don't understand why there is a "select" statement where there should be an "insert" or "update" statement.
I'm waiting for IBM to implement "merge" in DataStage, although that would complicate the UI as you need to tick two lists of columns - one set of "key" columns and one set of "when matched" columns, and there isn't a convenient place to put the second tickbox list.
I'm waiting for IBM to implement "merge" in DataStage, although that would complicate the UI as you need to tick two lists of columns - one set of "key" columns and one set of "when matched" columns, and there isn't a convenient place to put the second tickbox list.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 102
- Joined: Thu Sep 17, 2009 1:23 am
Oracle UPSERT
As in previous posts also it has been asked regarding your select statement, you can have a userdefined update or insert statement or automatically also you can have update and insert statement.
Rohit
I was being somewhat tongue-in-cheek with my answer, just wanted to point out the improper use of the word 'select' rather than insert or update in the DML.
I vaguely recall some stage (you've never stated which Oracle stage we're discussing) not having an "update" mode. You could insert or you could upsert but if you wanted a simple update you had to choose a user-defined upsert of update then insert and replace the insert with another copy of the update to satistfy the stage and ensure that no inserts could occur. That would be the only time you'd do something odd like that, in my humble opinion.
Haven't touched DataStage in going on three years so my memory might be a little off but that's what I recall.
I vaguely recall some stage (you've never stated which Oracle stage we're discussing) not having an "update" mode. You could insert or you could upsert but if you wanted a simple update you had to choose a user-defined upsert of update then insert and replace the insert with another copy of the update to satistfy the stage and ensure that no inserts could occur. That would be the only time you'd do something odd like that, in my humble opinion.
Haven't touched DataStage in going on three years so my memory might be a little off but that's what I recall.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers