Reorder data in one column based on data in another column I have the following table structure:

create table abc ( Name number , No number )


The data is like this:

Name | No
-----+-----
 100 |  11
 101 |  20
 102 |  12
 103 |  16
 104 |  13
 105 |  14
 106 |  15


I need to retain the exact values that are in the No column; I just need to modify the records the current values are associated with, so that the seq values are in the same order as the Name values.

Name | No
-----+-----
 100 |  11
 101 |  12
 102 |  13
 103 |  14
 104 |  15
 105 |  16
 106 |  20
I'm no pro on Oracle, but yo need something like this wrapped in an UPDATE statement,

SELECT *
FROM ( SELECT id, row_number() OVER (ORDER BY id) FROM dummy )
  AS t1(id, rn)
JOIN ( SELECT seq, row_number() OVER (ORDER BY seq) FROM dummy )
  AS t2(seq, rn)
USING (rn);


We look at the two columns id and seq


For id, we sort by id, and return the row number() and id
For seq, we sort by seq, and return the row_number() and seq


We have the same amount of rows for both, so we'll have the same amount of row_number(). Now we just join on row_number(). Tie-handling is undefined here.

You should probably rename seq to priority.


A priority can be static and global depending on the context. The value can matter, as in a priority queue or job scheduler where some priorities hold significance.
A seq by convention usually has gaps so the values don't matter, just the comparison (sometimes the uniqueness).


Here is one attempt at making this approach work in Oracle (dbfiddle):

UPDATE
  test tgt
SET
  seq =
  (
    SELECT sortedBySeq.seq
    FROM (SELECT test.*, ROW_NUMBER() OVER (ORDER BY Id  ASC) AS rn FROM test) sortedById
    JOIN (SELECT test.*, ROW_NUMBER() OVER (ORDER BY seq ASC) AS rn FROM test) sortedByseq
      ON sortedById.rn = sortedBySeq.rn
    WHERE sortedById.Id = tgt.Id
  )
;


Referencing the same table three times probably won't lend itself well to efficiency, but this is something to start with at least.