Oracle UPSERT

ssreeni3 · Post by **ssreeni3** » Mon Oct 01, 2012 3:38 am

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

ssreeni3 · Post by **ssreeni3** » Mon Oct 01, 2012 3:43 am

Correction to my post:
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 for Update?

I observed this in one of my existed jobs.Is there any specific meaning for it?

Thanks in Advance,
Srini

ray.wurlod · Post by **ray.wurlod** » Mon Oct 01, 2012 3:02 pm

Probably not. Depends what you mean by "is there".

ssreeni3 · Post by **ssreeni3** » Tue Oct 02, 2012 11:41 pm

Thanks Ray for your reply.

My doubt is:
Which scenarios,we may give the same Select statement to the Update query also.

We are using Change Capture Stage in the Job.

Thanks,
Sreeni

chulett · Post by **chulett** » Wed Oct 03, 2012 7:23 am

I honestly doubt you have any kind of a "select statement" in your target.

chandra.shekhar@tcs.com · Wed Oct 03, 2012 7:43 am

Can you please tell us when you say

Select statement to the Update query also

As for as I know, you can have as Insert statement, Update Statement or Both. If you are using a "select" statement then how can you achieve UPSERT mode ??

PhilHibbs · Post by **PhilHibbs** » Wed Oct 03, 2012 8:34 am

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.

rohitagarwal15 · Post by **rohitagarwal15** » Thu Oct 04, 2012 6:19 am

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.

chulett · Post by **chulett** » Thu Oct 04, 2012 7:20 am

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.

ssreeni3 · Post by **ssreeni3** » Fri Oct 05, 2012 1:27 am

Hi Experts,

Thanks for your inputs and valuble suggestions and openions.

Thnaks,
Sreeni