Page 1 of 1
Oracle EE Update issue
Posted: Thu Oct 12, 2006 9:44 pm
by kommven
While using update in Oracle EE (Upsert Mode)
(based on a Non-Key Value.)
Only updating the first occurance.
Any recommendations to avoid this situation?
-K
Posted: Thu Oct 12, 2006 11:14 pm
by ray.wurlod
What do you mean by "updating on a non-key value"? Are you using user-defined SQL?
Posted: Thu Oct 12, 2006 11:18 pm
by kommven
Its a generated statement.
I mean a repeating value. in a column.
Say If run update tableA set Col1 = 'a' wher col2 = 4 will update 100 rows in SQL.
In my Job
It runs only the first occurance.
Thats the situation.
-K
Posted: Thu Oct 12, 2006 11:34 pm
by chulett
What leads you to believe it 'runs only the first occurance'? Some specific examples would be good...
Posted: Fri Oct 13, 2006 12:13 am
by kommven
A SQL validation proved that.
Update Table1 Set CityName=Orchastrate.CityName Where CItyID=Orchastrate.CityID
is the genrated Query.
Initially, In Target CityName is NULL and CityID has a value.
AFTER JOB RUN
SQL iss returning results...
Chicago,9342
,9342
,9442
Dallas,9343
,9343
Posted: Fri Oct 13, 2006 12:47 am
by ray.wurlod
I'm forced to assume that "Orchastrate" misspelled is a result of your not using copy/paste. Get your Oracle DBA to intercept the command to see exactly what statement was executed.
If all else fails, join your data stream to the target table to make sure you get all the relevant rows updated; you may need to stage the results in a persistent Data Set prior to loading, to avoid locking issues.