Updating column so that it contains the row position

Question

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

you could probably use a CASE statement somehow, but two queries may be better. — dqhendricks, Jan 08 '11 at 06:30
@Col. Shrapnel: will work when Position column is blank, but not after user positions some of the items. — Salman A, Jan 09 '11 at 14:09
Nor any other proposed solution. it's just for initial filling. To move it up and down after that you will need to run another queries, simple ones. — Your Common Sense, Jan 09 '11 at 14:13

score 12 · Accepted Answer · edited Jan 26 '12 at 07:06

12

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;

edited Jan 26 '12 at 07:06

Salman A

262,204
82
430
521

answered Jan 08 '11 at 12:35

ajreal

46,720
11
89
119

Will test it tomorrow. Can you use the same table in update and select? – Salman A Jan 09 '11 at 14:21
Mchl, I had to swap ()rowNumberInit above ()table_position for this single query to work. As above, new_position always comes out to 1, if ()rowNumberInit is put first, before @row_number is used in ()table_position, new_position gives numbers in order. – Peter Barton Feb 09 '16 at 11:22

Salman A · Answer 2 · 2012-02-09T13:59:35.200

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 record

SET @current_group = NULL;
SET @current_count = NULL;

UPDATE 
content
SET Position = CASE
    WHEN @current_group = CategoryID THEN @current_count := @current_count + 1
    WHEN @current_group := CategoryID THEN @current_count := 1
END
ORDER BY CategoryID, Position -- <Column 3>, <Column 4>, ...

score 1 · Answer 3 · answered Jan 08 '11 at 11:20

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.

score 0 · Answer 4 · edited Jan 26 '12 at 07:06

0

Initial:

UPDATE content as uc 
SET Position = (
    SELECT count(*) 
    FROM content as sc 
    WHERE sc.CategoryId = uc.CategoryId AND sc.Position is not null)
WHERE uc.Position is null
ORDER BY uc.ContentId

Before insert:

UPDATE content
SET Position = Position+1
WHERE Position >= newPos AND CategoryId = newCat

edited Jan 26 '12 at 07:06

Salman A

262,204
82
430
521

answered Jan 08 '11 at 06:33

Gives me this error `SQL Error (1093): You can't specify target table 'uc' for update in FROM clause`. – Salman A Jan 08 '11 at 07:06

Updating column so that it contains the row position

4 Answers4

Added by Mchl:

Linked