Hi,
I have 6 columns in Table.No primary keys.
But unique index is created on first 5 columns.
I tried to update the table by taking these 5 index columns as key columns.
2 index columns have null values.
I have taken Oracle connector for Update.
Job went fine with no warnings.But record is not updated.
Please help me to sort out this issue
Thanks in advance,
srini
Update issue on Index columns:Oracle connector stage
Moderators: chulett, rschirm, roy
Even though you have a unique index, you cannot have a PK over those fields because Primary Keys don't allow nullable fields. And you can't simply pass NULL to those fields and expect an equality check to work.
You'll need to provide your own update DML to handle that. Simplest way I've found is to use NVL() to any possible nulls to an "in-band" value - something not found in the data. And you need to do it on both sides for each nullable field. For example, let's say A is one of your problem children, is a number and negative numbers are not possible:
WHERE NVL(source.A,-1) = NVL(target.A,-1)
With that in the DML, things should work fine.
You'll need to provide your own update DML to handle that. Simplest way I've found is to use NVL() to any possible nulls to an "in-band" value - something not found in the data. And you need to do it on both sides for each nullable field. For example, let's say A is one of your problem children, is a number and negative numbers are not possible:
WHERE NVL(source.A,-1) = NVL(target.A,-1)
With that in the DML, things should work fine.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
you should have a surrogate key for updates in such cases where you can replace the nulls with some value you are not expecting in the data such as 0, -9999 or ~, and then do a lookup and send records to update based on surrogate key you got from there.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.