multiple update statements

[dnat]

Hi,

From source i am splitting the records based on certain conditions and i need to update different columns based on the conditions.

What i am doing is :Take all the columns from source,send to different links according to conditions, change the required columns and send to funnel and combine it. So, i have all the columns from input that needs to be updated. But when there is no update for any column, i am just pulling it from source and using the same value.

Now, if i want to update only the required columns, i think the only way is to use multiple oracle stages. which i dont want to do..

Can we use the same oracle stages with multiple update statements?.

If i pass another column with a flag, say A to update one column, B to update another column and it goes through funnel and in the oracle stage, can i use this flag to differentiate different update statements?

ot is there any other way?

[biju.chiramel]

As per what I understood...

suppose A, B are the key columns C, D, E are to be updated and F, G, H not to be updated

then pass F, G, H as NULL values...

and use only one statement like

update TABLE
SET C = NVL(:3, C),
D = NVL(:4, D),
E = NVL(:5, E),
F = NVL(:6, F),
G = NVL(:7, G),
H = NVL(:8, H)
WHERE A= :1 AND B = :2

[dnat]

no, that is not my requirement..

i have different conditions based on which i have to update different columns

[chulett]

Now, if i want to update only the required columns, i think the only way is to use multiple oracle stages. which i dont want to do..

Want or don't want, doesn't matter. You'll need multiple stages. A Server job would support multiple links into a single stage but PX doesn't. Or as you've noted, you can do a lookup and pass the current value on the fields you don't want to change on that particular record.

[keshav0307]

that means if for example , the FLAG value is 3 then update column C, if 4 then D, if 5 then E etc.

Then this query should work:

update TABLE
SET C = DECODE(FLAG,3,:3, C),
D = DECODE(FLAG,4,:4, D),
E = DECODE(FLAG,5,:5, E),
F = DECODE(FLAG,6,:6, F)
WHERE A= :1 AND B = :2

[dnat]

Thanks for the responses..One more thing to ask..Is there a performance change if i update one field or 20 fields of a record?

[chulett]

No, nothing appreciable I would imagine. It's more about how long it takes to find the record to update, how many of the changed fields are in indexes, etc...