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_positionset position=table_position.new_positionwhere 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 rowNumberInitset position=table_position.new_positionwhere table_position.ContentID=content.ContentID;

Here is the solution that worked for me (hope it helps someone):

-- The following query re-populates the "Position" column with sequential numbers so that:-- a) sequence is reset to 1 for each "group"-- b) sequence is based on row number relative to each group depending on how ORDER BY is specified-- c) sequence does not disturb the original order but-- c.a) fixes NULLs so that they are moved to top-- c.b) fixes duplicate position values depending on how ORDER BY is specified-- ContentID is the primary key-- CategoryID is a foreign key-- Position column contains relative position of a recordSET @current_group = NULL;SET @current_count = NULL;UPDATE contentSET Position = CASE    WHEN @current_group = CategoryID THEN @current_count := @current_count + 1    WHEN @current_group := CategoryID THEN @current_count := 1ENDORDER BY CategoryID, Position -- <Column 3>, <Column 4>, ...

I think it would be very tedious to run additional queries all the time when you do some operations on the table. I would create a trigger that fires every time you want to insert/update something in the table.

In your case, a BEFORE UPDATE and BEFORE INSERT trigger would be advisable. If you also want to keep it clean after the deletion of an etntry, add an AFTER DELETE trigger.