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!

I'm not sure on why you want to re-order your rows. (That is what you are trying to do?)

Well I want to sort all rows on the sort order instead of alpha by the description. I just know they'll want something like "Other" at the bottom of the list, so I'd like to leave it sorting by sort order.

I'm not totally sure on what you are trying to do but any sorting you do should be done as you perform the select. You don't want to reorder all your rows.

Sorry if I've still misunderstood :(

I probably messed the explanation.. Here goes:

I need to update a sort column with a new set of numbers. I plan to update the column with 1..10 based on alpha of description. Select has a order by, but update does not.

Is there any way to update a column with a new value from a incrementing value based on a specific order (alpha of desc in this case) so I can keep sorting by data_sort?

Thx

Ok so you want to update the rows so instead of:

AAAAA 9
EEEEEE 7
FFFFFF 1
MMMMM 2
OOOOO 3
PPPPPP 6
SSSSSA 10
SSSSSS 4
TTTTTT 5
VVVVVV 8

..you'd have:

AAAAA 1
EEEEEE 2
FFFFFF 3
MMMMM 4
OOOOO 5
PPPPPP 6
SSSSSA 7
SSSSSS 8
TTTTTT 9
VVVVVV 10

...am I closer?

Bingo! Yes that is exactly what I'd like to do.

Hmm I think sub-selects can only be done with a select so you can't use a select within an update.

I'd go for the perl approach.


I hope that helps and that I haven't missed something glaringly obvious :(

Okay.... thanks for the help. I'll give it a try that way.. I was just hoping for a SQL solution, but there doesn't seem to be one outside of moving the data to a temp table and pulling it back, which you are essentially doing in code holding the fields.

Thanks for the help!

Yeah I know it is a bit of extra hassle having to use a perl script

Maybe an SQL guru (aka Alex) can come up with a pure SQL version.

Well, if you just want data_sort to be a number that can be used for sorting thing alphabetically you could do:

UPDATE table SET data_sort = ASCII(LOWER(data_desc));

These are MySQL functions, you'll need to find the equivalant for whatever sql server you are using.

Not sure why you just dont ORDER BY data_desc instead though?

Cheers,

Alex
--
Gossamer Threads Inc.

Here you have the pure sql version with cursor. Guess you use Transact-SQL, so the syntax you need is:

...
declare @di integer, @so integer
declare cur dynamic scroll cursor for
select data_id from var_category order by data_desc
...
select @so=1
open cur
while 1=1 begin
fetch next cur into @di
if sqlcode<>0 break --or "@@sqlstatus"
update var_category set data_sort=@so where data_id=@di --or "where current of cur"
select @so=@so+1
end
close cur
...

Hope it helps.

Regards,
Kris Kramarz-Fernandez