Update table with another column in the same table
<p>I have a table like this</p>

<p>Test_order</p>

<pre><code>    Order Num    Order ID   Prev Order ID
    987Y7OP89    919325     0
    987Y7OP90    1006626    919325
    987Y7OP91    1029350    1006626
    987Y7OP92    1756689    0
    987Y7OP93    1756690    0
    987Y7OP94    1950100    1756690
    987Y7OP95    1977570    1950100
    987Y7OP96    2160462    1977570
    987Y7OP97    2288982    2160462
</code></pre>

<p>Target table should be like below,</p>

<pre><code>Order Num   Order ID    Prev Order ID
987Y7OP89   919325      0
987Y7OP90   1006626     919325
987Y7OP91   1029350     1006626
987Y7OP92   1756689     1029350
987Y7OP93   1756690     1756689
987Y7OP94   1950100     1756690
987Y7OP95   1977570     1950100
987Y7OP96   2160462     1977570
987Y7OP97   2288982     2160462
987Y7OP97   2288900     2288982
</code></pre>

<p>Prev Order ID should be updated with the Order ID from the previous record from the same table.</p>

<p>I'm trying to create a dummy data set and update..but it's not working..</p>

<pre><code>WITH A AS
(SELECT ORDER_NUM, ORDER_ID, PRIOR_ORDER_ID,ROWNUM RID1 FROM TEST_ORDER),B AS (SELECT ORDER_NUM, ORDER_ID, PRIOR_ORDER_ID,ROWNUM+1 RID2 FROM TEST_ORDER)
SELECT A.ORDER_NUM,B.ORDER_ID,A.PRIOR_ORDER_ID,B.PRIOR_ORDER_ID FROM A,B WHERE RID1 = RID2
</code></pre>