Imagine you have this scenario – the order of a table which relies on its order has been muddled up by some bad code and you wish to sort it out with the least effort possible.
You can write a quick SQL script which will reset the order to the right set (seen on the right).
To find out what your order should be, include the ROW_NUMBER() command as below:
,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row
from DM_ReportColumns where [conditions]
Then you can run an update based on your primary keys (in this scenario, I have ReportID, Username and ColumnID as keys.
UPDATE DM_ReportColumns SET Orders = x.Row from ( select * ,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row from DM_ReportColumns where ReportID = 'A01' and Username = 'user1' ) as x WHERE x.ReportID = DM_ReportColumns.ReportID AND x.Username = DM_ReportColumns.Username AND x.ColumnID = DM_ReportColumns.ColumnID