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

Answer 1

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.