How to re-order rows in a table with incorrect ordering in SQL

Capture

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:

select *
,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

After

Capture2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s