Renumerate Row Sequence for PK 1

[cwsstins]

I am inserting rows from one table into another table. The source table has no primary key. The destination table has a combo primary key on two columns...CallID and Seq.

I've got data that looks like this in source table:
CallID Seq col_x
123456 1 x...
123456 2 y...
123456 1 y...
123456 2 z...
321654 1 x...
321654 1 y...
321654 2 x...

Before I can move it into the destination table, I need the Seq column renumbered like so:
CallID Seq col_x
123456 1 x...
123456 2 y...
123456 3 y...
123456 4 z...
321654 1 x...
321654 2 y...
321654 3 x...

I have no idea where to start...any suggestions appreciated.

 

[SantaMufasa]

Stinsman,

The answer to your need depends upon either a) the version of Oracle upon which these data reside or b) the highest installed version of Oracle to which you have access.

Please let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Unfortunately, I'm using 8.1.7.4 at the moment with no immediate plans for upgrade.

 

[SantaMufasa]

Actually, as long as you are using Oracle 8i (i.e., not Oracle 8.0.x), you can use the solution, below.

Your particular challenge is the "poster child" for Oracle's Analytical Functions. Using your original data, here is code that does what you want:

select callid
      ,row_number() over (partition by callid order by seq) seq
      ,col_x
from <source table>
/

 CALLID        SEQ COL_X
------- ---------- -----
 123456          1 x...
 123456          2 y...
 123456          3 y...
 123456          4 z...
 321654          1 x...
 321654          2 y...
 321654          3 x...

7 rows selected.

To cause these data to INSERT into your destination table, you simply slap an INSERT statement in front of the SELECT:

INSERT INTO <destination table>
select callid
      ,row_number() over (partition by callid order by seq) seq
      ,col_x
from <source table>
/

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

In revisiting you challenge, if you want the resulting rows to be in "COL_X" order within "CALLID", simply modify the code, above to read:

...select callid
      ,row_number() over (partition by callid [b]order by col_x[/b]) seq
      ,col_x
from <source table>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Works like a charm! As always, you have been most helpful, Santa!