Updating column so that it contains the row position

mysql, php, sql

Solution

This should work

update 
content,
(
  select 
  @row_number:=ifnull(@row_number, 0)+1 as new_position,
  ContentID 
  from content
  where CategoryID=1
  order by position
) as table_position
set position=table_position.new_position
where table_position.ContentID=content.ContentID;

But I would prefer to apply this first, to unset user defined variable

set @row_number:=0;

Added by Mchl:

You can do that in one statement like this

update 
content,
(
  select 
  @row_number:=ifnull(@row_number, 0)+1 as new_position,
  ContentID 
  from content
  where CategoryID=1
  order by position
) as table_position,
(
  select @row_number:=0
) as rowNumberInit
set position=table_position.new_position
where table_position.ContentID=content.ContentID;

Problem

This is the `content` table: ``` ContentID | CategoryID | Position | Other1 | Other2 =================================================== 1 | 1 | NULL | abcd | efgh 2 | 1 | NULL | abcd | efgh 3 | 1 | NULL | abcd | efgh 4 | 2 | NULL | abcd | efgh 5 | 2 | NULL | abcd | efgh 6 | 2 | NULL | abcd | efgh ``` These are the queries I'll be running: ``` SELECT ContentID FROM content WHERE CategoryID = 1 ORDER BY Position SELECT ContentID FROM content WHERE CategoryID = 2 ORDER BY Position ``` Now I want to implement move up, move down, move to top and move to bottom function for content. All I need to do is to populate the Position column with numbers: ``` ContentID | CategoryID | Position ================================= 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 4 | 2 | 1 5 | 2 | 2 6 | 2 | 3 ``` Is it possible to achieve this via single query in MySQL? Something like: ``` UPDATE content SET Position = <ROW_NUMBER> WHERE CategoryID = 1 ORDER BY Position UPDATE content SET Position = <ROW_NUMBER> WHERE CategoryID = 2 ORDER BY Position ```

Original source