Updating column so that it contains the row position
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
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.