Use Cursor to change Value in Column

[getjbb]

Hi,

I have a cursor that is being used to create an output file.
I want to use the same cursor to retieve values for one of the fields within the cursor The field is called NAME and what I am trying to do is to change the NAME if any two or more are alike. If any of the NAMES are the same I want to put a number at the end of it and update the record from before putting it into the output file. The first occurence of the name would not have a number at the end, the next occurrence of the name will be 2, and the next 3, etc. I am not sure how to do this. Will a temporary table to needed?

Annette

 

[SantaMufasa]

Annette,

If duplicate names in your table were:

Annette
Dave
Annette
Dave

...would you care if the "uniquing" routine produced these results?:

Annette
Dave
Annette_2
Dave_3

...or would the results need to be:

Annette
Dave
Annette_2
Dave_2

There's a good reason for the question, I promise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[getjbb]

Hi Mufasa,

The results should be:

Annette
Dave
Annette_2
Dave_2

getjbb

 

[SantaMufasa]

Annette,

To confirm that I understand things correctly, are you saying that you will be basing some sort of "ordinal" business intelligence on a virutally arbitrary "cardinal" change to your data? Specifically, how will your business be negatively affected if the "Dave_2", above, appears as "Dave_3" instead?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[getjbb]

Mufasa,

I guess it does not matter as long as the two names are not the same. I guess in a perfect world it would be ordinal.

getjbb

 

[SantaMufasa]

I agree, Annette, and with a bit more thought, I could probably come up with a not-too-inefficient method to resolve your need with a strictly ordinal solution within the same duplicate name.

In the meantime, let me just say that using a cursor (in a PL/SQL block of code) is rather inefficient (because the UPDATE statement would probably need to execute one time for each duplicate name) when compared to a single SQL UPDATE statement that executes once for ALL duplicates...much, much more efficient.

Following, then, is code that resolves your need.

Section 1 -- Sample data:

select * from annette;

NAME
-------
Annette
Dave
Annette
Dave
Annette
Dave
Annette
Dave
Nancy

Section 2 -- Single-statement, single-execution UPDATE code:

update annette
   set name = (select name ||'_'||to_char(rn)
                 from (select rowid ri, rownum rn from annette
                        where rowid > (select min(rowid)
                                         from annette innr
                                        where innr.name = annette.name)
                       ) middle
                where middle.ri = annette.rowid)
 where rowid > (select min(rowid)
                  from annette innr
                 where (innr.name = annette.name)
                )
/

select * from annette;

NAME
---------
Annette
Dave
Annette_1
Dave_2
Annette_3
Dave_4
Annette_5
Dave_6
Nancy

Let us know if this method meets with your approval.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[getjbb]

Mufasa,

The data I retrieve in my script is done using a cursor. So, in your example above I am assuming that annette is a temporary table created from the cursor select statement. Because I did not think I could perform the update you mentioned above on a cursor.

getjbb

 

[SantaMufasa]

First, Annette, my presumption was that the "uniquing" of the names is a one-time maintenance activity. Once you have caused the names to become unique, you would then place a unique contraint on the "NAME" column to prevent any duplicates from ever making their way into your data again.

Second, user-defined CURSORs in the Oracle World exist only in PL/SQL blocks of code. Additionally, Oracle CURSORs are read-only. You can, however, use the data from a CURSOR to generate UPDATEs, but, as I mentioned earlier, if you base your UPDATEs on the data coming from the CURSOR, then the UPDATEs have a one-to-one correspondence with rows coming from the CURSOR...that can be a ton of updates. As an example, if there are 10,000 rows in your table that have duplicates needing updating, then my presumption is that using CURSOR-based-UPDATE logic, you will be doing 10,000 UPDATE executions.

Conversely, if you have those same 10,000 rows to UPDATE using my code above, then you have one UPDATE execution against 10,000 rows...that is a far more efficient scenario than doing UPDATEs in a PL/SQL block based upon what you read in from a CURSOR.

I can certainly explain Oracle's infrastructure for doing CURSOR-based UPDATEs in a PL/SQL block, but if this is a one-time update, I don't follow why you would choose to (inefficiently) conduct that one-time update by executing thousands of UPDATE invocations in a PL/SQL when you can accomplish the same result in a fraction of the time with a single execution of an UPDATE statement (which, by the way, can occur once in your PL/SQL block that contains the CURSOR, if your heart is set upon executing the UPDATE in the same PL/SQL block as your CURSOR).

I'm just trying to understand better why you are holding tight to the idea of doing (what appears to be) a one-time UPDATE based upon a CURSOR.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[getjbb]

There are no available tables to update from my script. What I am during is using a cursor to retrive information and then writing that data to a flat file. But before I write it to the file I need to change the value of the field NAME. In the end the data will then be uploaded to a Production table which I do not have access to. I need some place to store the the data coming from the cursor and then make the one time update you mentioned. Will a temporary table be the best way to do this?

 

[SantaMufasa]

Then under these circumstances, I would say "yes" to the idea of a temporary table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[getjbb]

Thanks Mufasa for your help.