Re: Update by rownum

Daniel, If I just accept the fact you have data in two table and for now the data just happens to correspond to a one to one relationship where the first row in table A matches the first row in table B to the Nth row then here is a way to merge the existing data into a table.

select t1.position, t1.col1, t2.col1
from ( select (rownum as position, col1, col2) from A ) t1,

        ( select (rownum as position, col1, col2) from B) t2 where t1.position = t2.position

This only works as long as the realtionship between the two tables is one to one in physical order, which means it will not survive any DML activity against the tables since inserts into blocks with free space, deletes, or even an update might invalidate the one to one row relationship by position.

Still if the realtion I describe does exist for whatever reason you can use the above as input to a CTAS (create table as select) to build the table you want based on your posted description of the problem. But physical row order and the order in which Oracle returns rows is not guarenteed. Any solution that joins the rows prior to labeling the rows would fail.

HTH -- Mark D Powell -- Received on Tue Oct 18 2005 - 09:57:39 CDT