-1

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)

calumbrodie
  • 4,722
  • 5
  • 35
  • 63
  • Why do you want this? There are scenarios where this could be desirable (for example, to have your harddisk read consecutive sektors on the disk for consecutive rows) but that is very micro-optimizing. A good index will do 99% of the work for you, in-table-order really is not that important. – Konerak Mar 04 '11 at 16:16
  • The order is significant- it holds meaning in itself within the system. I've edited my question to remove the index (which is not relevant to the question). Thanks – calumbrodie Mar 04 '11 at 16:25

1 Answers1

1

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

Community
  • 1
  • 1
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • Thanks for your answer - that would work for the example I gave - but I was looking for a statement that I could run. There is additional complexity that would mean this wouldn't work in my case. Sorry for not including this in the original question - I'll edit it now to explain better – calumbrodie Mar 04 '11 at 16:32