SQL Order By and Update a Column-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.