StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReindexing a position column in mysql
text
Body
copied!<p>This is a MySQL question:</p> <p>I have a table with the following structure.</p> <pre><code>reference position parent </code></pre> <p>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).</p> <p>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).</p> <pre><code>------------------------------------------------------ | 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 | |-----------------|--------------------|--------------| </code></pre> <p>Desired</p> <pre><code>------------------------------------------------------- | 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 | |-----------------|--------------------|--------------| </code></pre> <p>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)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload