Reorder data in one column based on data in another column

oracle

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

Best Answer

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.

Related Solutions

What to call a column that references another row in the same table

There are several things in play here. A table that has a self-referential key is said to have a reflexive foreign key, though that doesn't look like it applies here because you'll have missing values.

What you're looking for is a hierarchical query, which can be achieved in Oracle by using a CONNECT BY clause.

They can be a bit difficult to get your head around at first, but there are plenty of good examples on the internet. A good one to start with is here. For once, the official documentation is quite clear too!

Something like:

SELECT id, bar, reference_id, LEVEL
FROM yourtable
CONNECT BY PRIOR reference_id = id;

... will get you started on your data.

Oracle – How to Perform Update Join on Tables

The error points to a syntax problem. When you want to update multiple columns at once, you list all of the columns to be updated first, followed by all of the values to use (in the same order that the columns are listed). For example:

UPDATE CONTACT C
SET (C.PHONE, C.FAX) = (
    SELECT P.PHONE, P.FAX
    FROM PERSON P
    WHERE P.PERSON_ID = C.PERSON_ID
)
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

(You'll have to check my logic, because I'm not entirely clear on your relationships.)

Or, instead of doing an update with a correlated sub-query (as above), you can write it as a merge:

MERGE INTO CONTACT C
USING (
    SELECT PERSON_ID, PHONE, FAX
    FROM PERSON
) P
    ON (P.PERSON_ID = C.PERSON_ID)
WHEN MATCHED THEN UPDATE
    SET C.PHONE = P.PHONE, C.FAX = P.FAX
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

And another way:

UPDATE 
    ( SELECT c.PHONE, c.FAX, 
             p.PHONE AS P_PHONE, p.FAX AS P_FAX
      FROM PERSON p
        INNER JOIN CONTACT c ON c.PERSON_ID = p.PERSON_ID
      WHERE c.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
        )
    ) x
SET 
    PHONE = P_PHONE, FAX = P_FAX ;

Best Answer

Related Solutions

What to call a column that references another row in the same table

Oracle – How to Perform Update Join on Tables

Related Question