ALTER TABLE, ORDER BY,.. keep all columns intact except 1...

[lordvader]

Let's say:

Primary Key | UID's | Additional Info 1 |  Additional Info 2

1 | 5 | foo5 | bar5

2 | 3 | foo3 | bar3

3 | 4 | foo4 | bar4

 

Primary Key is autoincrement

UID is non-sequential, but unique numbers

 

How do I sort this table so that UID is sequential again (lowest to highest) while keeping their associations with additional info 1,2, and keeping the primary keys where they are?

1 | 3 | foo3 | bar3

2 | 4 | foo4 | bar4

3 | 5 | foo5 | bar5

 

Thanks

[mezise]

Hi,

 

try this: copy the structure of the table, execute query

INSERT INTO newTable SELECT
  NULL, `UID's`, `Additional Info 1`,  `Additional Info 2`
FROM oldTable ORDER BY ...

Delete old table, rename new table.

[fenway]

This is a continuation from this thread... you're obviously confused about PK and UID, since they should be one and the same.

 

You don't sort the data, you sort the output!

[lordvader]

I'm still learning mysql and the hard part is wrapping my head around all the nuances - every practice problem is so specific to the given example story that if I want to try variations, I'm lost because I don't know mysql's limitations and abilities.

[crtreedude]

Is it a problem with mysql or just sql in general. It would seem to me that you are used to ISAM type databases or flat files and are trying to absorb how SQL itself works. If so, try a tutorial on SQL, it should help. Especially design.

 

Manuals usually just tell you how to use the features, not why you want to.

[fenway]

I'm still learning mysql and the hard part is wrapping my head around all the nuances - every practice problem is so specific to the given example story that if I want to try variations, I'm lost because I don't know mysql's limitations and abilities.

Then explain what you actually want to do.