How to update using column in same table (ORACLE)

Question

I have a question to ask.

There is a Table name 'T1' with columns 'empno(pk)', 'deptno', 'sort_order'

sort_order column, is bit messy. It's works well but looks bad... so I want update rows using Rank() over.

RANK() OVER(PARTITION BY deptno ORDER BY sort_order ASC) AS new_sorder

I have no idea how to update sort_order column with the value of NEW_SORDER. I tried with 'MERGE INTO' It works really well, but I want to know is there any way NOT using 'MERGE INTO'.

This is what I tried...

UPDATE  t1 A 
set A.sort_order = (SELECT B.sort_order FROM
(SELECT     empno,
            deptno,
            RANK() OVER( PARTITION BY deptno
            ORDER BY     sort_order ASC ) AS new_sorder
FROM    t1 B
ORDER BY deptno, sort_order) B
WHERE A.empno = B.empno);

I solved the problem,(I don't know how I solved.... wth....) and this is what I used.(there is one more column name 'dept_order' use for ordering results)

UPDATE t1 a SET sort_order = (
Select new_order 
FROM (
select empno,deptno, rank() over (partition by deptno, dept_order
                                 order by sort_order) new_order 
        FROM t1
        ) b
WHERE a.empno = b.empno 
AND a.deptno = b.deptno );

Answer 1

You can use update with subquery as follows:

UPDATE T1 A
   SET
    A.SORT_ORDER = (
        SELECT B.NEW_SORDER
          FROM (
            SELECT EMPNO,
                   RANK() OVER(
                       PARTITION BY DEPTNO
                        ORDER BY SORT_ORDER ASC
                   ) AS NEW_SORDER
              FROM T1 B
        ) B 
         WHERE A.EMPNO = B.EMPNO
    );