This is a MySQL question:
I have a table with the following structure.
reference
position
parent
Every item is meant to have a position, and each position should only be used once. The positions should increment (so if there are 40 items then the positions should go from 1-40).
However the data in this table is all over the place (the same position being used more than once, some rows without a position). I'd like to reset the table by re-indexing the positions. I'd like to respect the existing orders (even if they are not perfect) when doing this (if at all possible - if not the positions can be discarded).
------------------------------------------------------
| reference | position | parent |
|-----------------|--------------------|--------------|
|ASHDFNS |2 |89 |
|BSHDFNS |2 |89 |
|CSHDFNS |1 |89 |
|DSHDFNS |100 |89 |
|ESHDFNS |8 |89 |
|FSHDFNS |22 |89 |
|ASHDFNS |1 |11 |
|BSHDFNS |22 |11 |
|CSHDFNS |333 |11 |
|-----------------|--------------------|--------------|
Desired
-------------------------------------------------------
| reference | position | parent |
|-----------------|--------------------|--------------|
|CSHDFNS |1 |89 |
|ASHDFNS |2 |89 |
|BSHDFNS |3 |89 |
|ESHDFNS |4 |89 |
|FSHDFNS |5 |89 |
|DSHDFNS |6 |89 |
|ASHDFNS |1 |11 |
|BSHDFNS |2 |11 |
|CSHDFNS |3 |11 |
|-----------------|--------------------|--------------|
EDIT: Sorry, simply auto increment the position column won't work as the table describes positions of items within multiple parents (and items can have more than one parent)
create a table with similar schema, but make the column position as auto_increment
after that insert from old_table order by whatever order you desire
insert into new_table select reference, 0, parent from old_table
order by if (position is null, 99999, position);
after that, rename old_table, rename new_table to old_table
Otherwise, define a user variables to represent the position
like this - update and select the same table problem in mysql
or this - Updating column so that it contains the row position