CODEWITHSUNDEEP
sql
wdonahoe
wdonahoe

Reputation: 1073

Update an ordinal column based on the alphabetic ordering of another column

I have a table representing a system of folders and sub-folders with an ordinal m_order column.

Sometimes sub-folders are sorted alphanumerically, others are sorted by date or by importance.

I recently had to delete some sub-folders of a particular parent folder and add a few new ones. I also had to switch the ordering scheme to alphanumeric. This needed to be reflected in the m_order column.

Here's an example of the table:

+-----+-----------+-----------+------------+
| ID  | parent    |   title   | m_order    |
+-----+-----------+-----------+------------+
| 100 | 1         | docs      | 3          |
| 101 | 1         | reports   | 2          |
| 102 | 1         | travel    | 1          |
| 103 | 1         | weekly    | 4          |
| 104 | 1         | briefings | 5          |
| ... | ...       | ...       | ...        |
+-----+-----------+-----------+------------+

And here is what I want:

+-----+-----------+-----------+------------+
| ID  | parent    |   title   | m_order    |
+-----+-----------+-----------+------------+
| 100 | 1         | docs      | 3          |
| 101 | 1         | reports   | 4          |
| 102 | 1         | travel    | 5          |
| 200 | 1         | contacts  | 2          |
| 201 | 1         | admin     | 1          |
| ... | ...       | ...       | ...        |
+-----+-----------+-----------+------------+

Upvotes: 2

Views: 529

Answers (3)

Cee McSharpface
Cee McSharpface

Reputation: 8725

I needed both t-sql and Oracle versions of this. To save future readers the struggle with the subtle differences in the ORA UPDATE syntax, here it is, shamelessly ripped off Gordon Linoff's answer:

update (
    with toupdate as (
        select 
            m.primarykey,
            row_number() over(partition by parent order by title) as seqnum
        from menu m
    )
    select m.primarykey, t.seqnum from menu m inner join toupdate t on t.primarykey=m.primarykey
)
set m_order = t.seqnum;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

I would do this with a simple update:

with toupdate as (
      select m.*, row_number() over (partition by parent order by title) as seqnum
      from menu m
     )
update toupdate
    set m_order = toupdate.seqnum;

This restarts the ordering for each parent. If you have a particular parent in mind, use a WHERE clause:

where parentid = @parentid and m_order <> toupdate.seqnum

Upvotes: 3

wdonahoe
wdonahoe

Reputation: 1073

After deleting the old folders and inserting the new records, I accomplished the reordering by using MERGE INTO and ROW_NUMBER():

DECLARE @parentID INT
...

MERGE INTO menu
USING (
        SELECT ROW_NUMBER() OVER (ORDER BY title) AS rowNumber, ID
        FROM menu
        WHERE parent = @parentID
      ) AS reordered
ON menu.ID = reordered.ID
WHEN MATCHED THEN
    UPDATE
    SET menu.m_order = reordered.rowNumber

Upvotes: 1

Related Questions