moving id numbers in a table?

[garyed]

I have a table with an id incremented from 1 to 100. I want to add a new record and have its id number to be 33 and move all the numbers from 33 up one.  How can I do that without manually editing every record from 33 up? 

[Barand]

An id is a unique identifier for a record. It should retain that same id for the whole lifetime of the record. The id should not be reused.

 

What is there about that record that says it must be no 33?

[garyed]

An id is a unique identifier for a record. It should retain that same id for the whole lifetime of the record. The id should not be reused.

 

What is there about that record that says it must be no 33?

 

I use the database to populate a drop down menu and the record needs to be in that particular spot because it was one that I overlooked when creating the database.  It has nothing to do with alphabetical order so there's no other way to sort the order unless I add another field and number every record again.  I would think there is an easier way.

 

 

[kicken]

Your sort order should be another field, not based on the ID field. Then you can adjust the order of your records however you want without having to mess around with the IDs.

 

Add a column named SortOrder to your table, make it an INT column and number the records in the order you want them to appear. Alter your SELECT for those records to add ORDER BY SortOrder so it will properly order them.

[garyed]

Your sort order should be another field, not based on the ID field. Then you can adjust the order of your records however you want without having to mess around with the IDs.

 

Add a column named SortOrder to your table, make it an INT column and number the records in the order you want them to appear. Alter your SELECT for those records to add ORDER BY SortOrder so it will properly order them.

I understand how to do that but I'm just amazed there isn't a simple command line that will do what I want to do instead of numbering every record. What if you had 10,000 records and you you just wanted to add one record to be sorted a certain way? It doesn't make sense that there isn't a better way. Isn't there a command that would move every id number up +1 at a certain point?

[garyed]

I found a way to do it in a few steps. First you have to get rid of the primary key & then its easy.

 

The table name is "first2" and the field name is "id".

 

I used these  commands:

alter table first2 modify id INT NOT Null;
alter table first2 drop primary key;
update first2 set id = id+1 where id > 32;
update first2 set id = 33 where id =102; 

It raised all the numbers from 33 up 1 so I could change 102 which was the new record that I added  to 33.

Now I can sort them to display in the order I want.