Update not working

[ag_ram]

Hi All,

I have a following job design
sequential file -- transformer - Oracle enterprise stage.

I need to update the table incase of matching key columns otherwise i need to insert a new row.
but even if there are matching rows, it still inserts a new row resulting in duplicates.
i have verified key columns are of same type,
I ran the job with only one key column which is a char field of one length and still update is not working. pls help me to solve the problem,
There are no key columns in my table.
i am using auto generated update and Insert option.

[ArndW]

Are you using custom SQL , and if so, please post it. If you are getting duplicate "key" columns, then most likely your table DDL is not correct. You might look at that and/post it here as well.

[Kryt0n]

Hi All,

I have a following job design
sequential file -- transformer - Oracle enterprise stage.

I need to update the table incase of matching key columns otherwise i need to insert a new row.
but even if there are matching rows, it still inserts a new row resulting in duplicates.
i have verified key columns are of same type,
I ran the job with only one key column which is a char field of one length and still update is not working. pls help me to solve the problem,
There are no key columns in my table.
i am using auto generated update and Insert option.

Are you doing "Insert then Update" or "Update then Insert"? (That's a setting in Oracle Stage btw, not a general query)

[keshav0307]

the table must have unique index on the key columns

[Sreenivasulu]

If possible I would rather perform insert and update in separate jobs.

Datastage has not yet implemented transaction handling features like 'auto commit','begin and close transation' and other database critical features which are the need of the hour.

Regards
Sreeni

[kduke]

You can insert by looking up the record in the target to figure out if it is an insert or an update. You could do this in 2 links or 2 different jobs.