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.
精彩评论