Reordering data in a table

Question

If I have this data in a table in MSSQL which represents a horizontal row of objects and their coordinates:

row      id      value
35395   776130 0
35395   776131 20
35395   776132 40
35395   776133 60
35395   776134 80
35395   776135 100
35395   776136 120
35395   776137 140
35395   776138 160
35395   776139 180
35395   776140 200

And I want to change the values so that they are ordered in descending order (flip the row 180degrees):

row      id      value
35395   776130 200
35395   776131 180
35395   776132 160
35395   776133 140
35395   776134 120
35395   776135 100
35395   776136 80
35395   776137 60
35395   776138 40
35395   776139 20
35395   776140 0

How can I do this using an update statement without doing it one by one?

Answer 1

I don't know the nature of the problem, but only based on your example, can't you update all rows doing

UPDATE `thetable` SET value = 200 - value

Again, I don't know the nature of the problem so it might not be this easy, but then again this seem to work in your simplified example and might as well work in your real world problem.

Answer 2

With help from Seb's answer I have formulated a solution:

declare @maxcoord int;
declare @rowid int; set @rowid = 35395

select @maxcoord = max(value) from table where row = @rowid

update table set value = @maxcoord - value 
where row = @rowid

I think I will create a stored procedure for this as I don't think this will be the only time it will be used :)

Answer 3

If you are using SQL Server 2005 or later, this might do it for you. The advantage is that it's a single query.

with T1(rn,row,id,value) as (
  select
    rank() over (
      order by value,row,id
    ),
    row,id,value
  from T
), T2(rn,value) as (
  select
    row_number() over (
      order by value desc
    ), value
  from T
), T12(row,id,value,vnew) as (
  select row,id,T1.value,T2.value
  from T1 join T2 on T1.rn = T2.rn
)
  update T12 set
    value = vnew;

I assumed the "value" column was originally increasing, and you want it decreasing, but I allowed for the possibility of two consecutive values being the same, so long as (row,id) is a key for the table.

There are other possibilities. For example, if (row,id) is a key, and you want to reverse the order of the "value" values from whatever position they're at to the "mirror" position, you could do this:

with T1(rn,row,id,value) as (
  select
    rank() over (
      order by row,id
    ),
    row,id,value
  from T
), T2(rn,value) as (
  select
    rank() over (
      order by row desc,id desc
    ), value
  from T
), T12(row,id,value,vnew) as (
  select row,id,T1.value,T2.value
  from T1 join T2 on T1.rn = T2.rn
)
  update T12 set
    value = vnew;

With the right indexes, this could be relatively efficient.

Answer 4

Do you want to actually update the rows, or do you just want to display them in reverse order? If it's just display order, try this:

SELECT row, id, 200-value as Value
  FROM YourTable
 ORDER BY (200-value) DESC