I need to update a table's sort column so that I can alphabetize the list. Here's the data and what I tried:
select * from var_category order by data_desc
Gives:
data_id data_desc data_sort data_status
--------- ----------------------- ----------- -----------
9 AAAAA 9 1
7 EEEEEE 7 1
1 FFFFFF 1 1
2 MMMMM 2 1
3 OOOOO 3 1
6 PPPPPP 6 1
10 SSSSSA 10 1
4 SSSSSS 4 1
5 TTTTTT 5 1
8 VVVVVV 8 1
and I want to do this:
declare @mycount tinyint
select @mycount = 0
update var_category set data_sort = (select @mycount = @mycount + 1) order by data_desc
..but update doesn't have an order by. You can't create views with order...
I guess I could move it to a temp table and swap it back, but I need to do this to several large tables. Any ideas would be helpful.
Thanks!
select * from var_category order by data_desc
Gives:
data_id data_desc data_sort data_status
--------- ----------------------- ----------- -----------
9 AAAAA 9 1
7 EEEEEE 7 1
1 FFFFFF 1 1
2 MMMMM 2 1
3 OOOOO 3 1
6 PPPPPP 6 1
10 SSSSSA 10 1
4 SSSSSS 4 1
5 TTTTTT 5 1
8 VVVVVV 8 1
and I want to do this:
declare @mycount tinyint
select @mycount = 0
update var_category set data_sort = (select @mycount = @mycount + 1) order by data_desc
..but update doesn't have an order by. You can't create views with order...
I guess I could move it to a temp table and swap it back, but I need to do this to several large tables. Any ideas would be helpful.
Thanks!