MySQL query for initial filling of order column

Question

Sorry for vague question title.

I've got a table containing huge list of, say, products, belonging to different categories. There's a foreign key column indicating which category that particular product belongs to. I.e. in "bananas" row category might be 3 which indicates "fruits".

Now I added additional column "order" which is for display order within that particular category. I need to do initial ordering. Since the list is big, I dont wanna change every row by hand. Is it possible to do with one or two queries? I dont care what initial order is as long as it starts with 1 and goes up.

I cant do something like SET order = id because id counts from 1 up regardless of product category and order must start anew from 1 up for every different category.

Example of what I need to achieve:

ID | product | category | Order

1 | bananas | fruits | 1

2 | chair | furniture | 1

3 | apples | fruits | 2

4 | cola | drinks | 1

5 | mango | fruits | 3

6 | pepsi | drinks | 2

(category is actually a number because it's foreign key, in example I put names just for clarification)

As you see, order numbers start anew from 1 for each different category.

Answer 1

Sounds like something a SQL procedure would be handy for.

Answer 2

Why not just set the order to the category? That is, why not:

update Table
set SortOrder = Category;

As an aside, you cannot have a column named order -- that is a reserved word in SQL.