Re: Duplicate Ids

On Jan 23, 7:25 pm, mnaf..._at_gmail.com wrote:
> How do I update a dup ID with a suffix "_sm"
>
> For example I have data that's coming from three different sources
> 1. ABC
> 2. DEF
> 3. XYZ
>
> The first data file have jsmith and so does the other two.......after
> checking for my dups I want to prefix them in a priority order, by
> which I mean I want any user that has DEF as a source be as is and XYZ
> to be changed next and then ABC
>
> How do I go about changing jsmith to jsmith_sm for XYZ and then
> jsmith_sm2 for ABC
>
> Working in oracle 8i

Being that the data is in files I would append a file indicator (or maybe sequence) in the insert process. Then you can select the data using non-unique key, indicator order. If there are never more than one duplicate value per file you can then write an update that keys on the indicator to modify the values. If you need to use the _sm suffix for data from the third file when the value does not exist in the second file then you can use the analytic function row_number to renumber the rows based on the key, indicator sort order and use this for guiding the final update.

HTH -- Mark D Powell -- Received on Wed Jan 24 2007 - 08:36:54 CST