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
update a column in the table using oracle stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: update a column in the table using oracle stage
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 wrote: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
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
if you update with a sequel both should get updated ... how are you doing it?kirankota79 wrote: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.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
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.
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 satisfykirankota79 wrote: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.
Code: Select all
(SR_ID = ORCHESTRATE.SR_ID)
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.