Hi Tom,

is it possible to update table rows in prefered row order by using sequence value?
Example:
CREATE TABLE T (ID NUMBER PRIMARY KEY, ORDER_COL NUMBER);
CREATE SEQUENCE SQ_T;

INSERT INTO T(ID, ORDER_COL) VALUES(2,2);
INSERT INTO T(ID, ORDER_COL) VALUES(4,4);
INSERT INTO T(ID, ORDER_COL) VALUES(3,3);
INSERT INTO T(ID, ORDER_COL) VALUES(1,1);
INSERT INTO T(ID, ORDER_COL) VALUES(5,5);

And now i want to update column ORDER_COL BY USING sequence SQ_T, but i want to process UPDATE statement in ordering rows by ID.
So this UPDATE is not correct - SQ_T is not using "in order by ID"
UPDATE T SET ORDER_COL = SQ_T.NEXTVAL;
Result is:
ID ORDER_COL
---------- ----------
1 4
2 1
3 3
4 2
5 5

Is there any possibility how to do it? In UPDATE statement it is not possible to use ORDER BY clause. I can use MERGE:
MERGE INTO T
USING (SELECT ID FROM T ORDER BY ID) ST ON (ST.ID = T.ID)
WHEN MATCHED THEN UPDATE SET T.ORDER_COL = SQ_T.NEXTVAL;

But this is not as expected too:
ID ORDER_COL
---------- ----------
1 4
2 1
3 3
4 2
5 5

I want this result:
ID ORDER_COL
---------- ----------
1 1
2 2
3 3
4 4
5 5

Thanks,
Robert

CHRIS>update t set order_col = null;

5 rows updated.

Elapsed: 00:00:00.11
CHRIS>
CHRIS>select * from t
  2  order  by 1;

        ID  ORDER_COL
---------- ----------
         1
         2
         3
         4
         5

Elapsed: 00:00:00.23
CHRIS>
CHRIS>update t
  2  set    order_col = (
  3    with rws as (
  4      select row_number() over (order by id) r , id from t
  5    )
  6      select r from rws where t.id = rws.id
  7    );

5 rows updated.

Elapsed: 00:00:00.11
CHRIS>
CHRIS>select * from t
  2  order  by 1;

        ID  ORDER_COL
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5