update a column in the table using oracle stage

[kirankota79]

ho can i update a column without using a key column in the oracle stage. for example there are around 150 columns in my table and i want to to transform only one column and update the table. i have no primary key column in the table to use the update mode in the oracle stage. what is the the best way to update that particular column. i can use sql loader taking all the 150 columns and load them back, but i dont want to do that.


thanks in advance

[chulett]

Key doesn't mean Primary Key in this context, it just means the field(s) to use in the where clause. How would you do this in straight SQL? You do the same here, mark the fields needed in the where clause as 'key' field, anything else will be updated.

[sud]

ho can i update a column without using a key column in the oracle stage. for example there are around 150 columns in my table and i want to to transform only one column and update the table. i have no primary key column in the table to use the update mode in the oracle stage. what is the the best way to update that particular column. i can use sql loader taking all the 150 columns and load them back, but i dont want to do that.


thanks in advance

But to update a table you WILL need key columns, other wise you will end up updating all the records in the table. What exactly is your intention, or, let me ask this, if you were doing the whole thing using only SQL, how would you do it? Even with SQL loader are you saying that you will delete all records from the table and reload?

[kirankota79]

sometimes in the tables, the key column has duplicates, and when updating the first value is getting updated and second(duplicate ) is not getting updated.

[sud]

sometimes in the tables, the key column has duplicates, and when updating the first value is getting updated and second(duplicate ) is not getting updated.

if you update with a sequel both should get updated ... how are you doing it?

[kirankota79]

UPDATE
SR_SALES_NOC
SET
DSO_CIRCUIT_ID_1 = ORCHESTRATE.DSO_CIRCUIT_ID_1, DSO_CIRCUIT_ID_2 = ORCHESTRATE.DSO_CIRCUIT_ID_2, DSO_CIRCUIT_ID_3 = ORCHESTRATE.DSO_CIRCUIT_ID_3, DSO_CIRCUIT_ID_4 = ORCHESTRATE.DSO_CIRCUIT_ID_4
WHERE
(SR_ID = ORCHESTRATE.SR_ID)

this is the update sequel iam using, if the sr_id has a duplicate value, will it update that record.

[sud]

UPDATE
SR_SALES_NOC
SET
DSO_CIRCUIT_ID_1 = ORCHESTRATE.DSO_CIRCUIT_ID_1, DSO_CIRCUIT_ID_2 = ORCHESTRATE.DSO_CIRCUIT_ID_2, DSO_CIRCUIT_ID_3 = ORCHESTRATE.DSO_CIRCUIT_ID_3, DSO_CIRCUIT_ID_4 = ORCHESTRATE.DSO_CIRCUIT_ID_4
WHERE
(SR_ID = ORCHESTRATE.SR_ID)

this is the update sequel iam using, if the sr_id has a duplicate value, will it update that record.

It will update all records that satisfy

Code: Select all

(SR_ID = ORCHESTRATE.SR_ID)

[chulett]

So... mark the SR_ID column as the 'Key' field to get it in the generated where clause. And yes, all records that match will be updated.

There's no DataStage magic here, just plain old ordinary SQL.