Oracle EE Update issue

kommven · Post by **kommven** » Thu Oct 12, 2006 9:44 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Oct 12, 2006 11:14 pm

What do you mean by "updating on a non-key value"? Are you using user-defined SQL?

kommven · Post by **kommven** » Thu Oct 12, 2006 11:18 pm

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

chulett · Post by **chulett** » Thu Oct 12, 2006 11:34 pm

What leads you to believe it 'runs only the first occurance'? Some specific examples would be good...

kommven · Post by **kommven** » Fri Oct 13, 2006 12:13 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Oct 13, 2006 12:47 am

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.