开发者

Update a variable number of rows each with different values in a single SQL command?

I have a table of ordered data like this

ID   ORDER
12     1
13     2
14     3
15     4
...
200    189
201    190
...

I would like to be able to update a few or all of their "order"s. How should I do that?

For example, I might switch the ordering between ID=12 and ID=13 so it'd go like

ID   ORDER
12     2
13     1
14     3
15     4
...

This would just be a simple UPDATE TABLE SET ORDER=1 WHERE ID=13 SET ORDER=2 WHERE ID=12

But if I wanted to move ID=200 all the way to the top,..

ID   ORDER
12     2
13     3
14     4
15     5
...
200    190
201    1
...  

then everything would have to be updated..? How do I do that? Is there a better way? Decimals?

edit: I'm using MSSQL btw

edit:clarification of use: I have a table with a long list of URL links, and the order of 开发者_如何学Cthose links matter. I want to be able to rearrange their order. I have a web page that retrieves that list from the db, displays the names as an unordered-list, and I can rearrange the items on that list. I'm stuck on how to get the newly ordered list's order updated into the database.


If you want to move an item up to the top, and update the order of all the other in a single statment, you can do the following:

UPDATE MyTable
SET    Order = (CASE Order WHEN 190 THEN 1
                           ELSE Order + 1
                           END)
WHERE  Order BETWEEN 1 AND 190


To move Id 200 to the top, you have to do this:

1) Take everything which is ordered before Id 200 and increase the order by 1

update MyTable 
set Order = Order + 1 
where Order < (select Order from MyTable where Id = 200)

2) Put Id 200 to the top of the list (Order = 1)

update MyTable 
set Order = 1
where Id = 200


I think, What you are looking is

Select * from TableName order by ID Desc/Asc

You want to Order the rows, Ascending or Descending


I don't think the above question quite answers it. I believe the answer to your question is what you already suspect - to change order = 200 to order = 1, you will have to rescore every other value, or use a number format with decimals.

However, I strongly suspect that if you elaborated more on why you need to do it this way, we could chime in with some better recommended methods.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜