SQL Order By and Update a Column

Question

I have the same issue listed in this SO question, but I have tried the solution and I am getting a SQL error 1288- I'm thinking I am poorly translating SQL server to SQL or this method has been deprecated. Is there a current way of doing this?

I need to Order By a column to then iterate through my new column, adding a new order and better upkeep for future inserts and deletes. My current SQL query for updating the new column looks like this ( same as the given solution above ):

With cte As
    (
    SELECT ColumnToOrderBy,NewColumn,
    ROW_NUMBER() OVER (ORDER BY ColumnToOrderBy) AS i
    FROM TableToUpdate
    )
UPDATE cte SET NewColumn=i

@jarlh Right, I'm using Heidi running on MySQL – CoolHands Jan 09 '23 at 16:45 — CoolHands, Jan 09 '23 at 16:45

forpas · Answer 1 · 2023-01-09T19:45:19.883

1

Your code does not work because MySql does not support (yet) updatable ctes.

Assuming that ColumnToOrderBy is unique or the primary key, so that the order is defined uniquely, you can do it with a self join in the UPDATE statement:

UPDATE TableToUpdate t
INNER JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY ColumnToOrderBy) rn FROM TableToUpdate) r
ON r.ColumnToOrderBy = t.ColumnToOrderBy
SET t.NewColumn = r.rn;

See the demo.

edited Jan 09 '23 at 19:45

answered Jan 09 '23 at 19:36

forpas

160,666
10
38
76

ColumnToOrderBy is not unique or this would be a great solution, appreciate the help! – CoolHands Jan 17 '23 at 21:21
@CoolHands if `ColumnToOrderBy` is not unique then `ROW_NUMBER() OVER (ORDER BY ColumnToOrderBy)` may return arbitrary values. So, how does your query work? The order of the rows must be uniquely defined. – forpas Jan 19 '23 at 15:53
Previously this table was sorted automatically by legacy gen files and it only focused loosely on that OrderBy statement. Now I want to have user sorting CRUD ops, hence the need for a new ordered column. Since the current ordering is not unique I am able to just do a mostly correct in place update – CoolHands Jan 20 '23 at 16:15

score 0 · Accepted Answer · answered Jan 09 '23 at 17:14

I went around a cte and just created an explicit temp table with the two necessary columns, ran the ROW_NUMBER function and then set the original table's column to the new sorted temp column. Code Below:

CREATE TEMPORARY TABLE temp 
    (ColumnToOrderBy INT,
    NewColumn INT);
INSERT INTO temp
    SELECT PrimaryKey,
    ROW_NUMBER() OVER (ORDER BY ColumnToOrderBy) AS NewColumn
    FROM TableToUpdate;
UPDATE TableToUpdate update
INNER JOIN temp t ON update.ColumnToOrderBy= t.ColumnToOrderBy
SET update.NewColumn= t.NewColumn;

DROP TABLE temp;

If working with large tables I don't imagine this is a quick or efficient solution, but for a small fix this works.

SQL Order By and Update a Column

2 Answers2