Need To Increase ID #'s By One

[jimbo]

I just found out that I skipped a game on my schedule, and I need to insert it into the db.[nbsp][nbsp]But all of my db abstraction uses </font><font face="Courier" size="3">ORDER BY gameid</font><font face="Verdana, Arial" size="2">, so I can't add it to the db as the last row.

Is there any way to increase all values in a column after a specified row?[nbsp][nbsp]I'll then insert this record with the appropriate ID#.

Thanks :)

-jim

[PaulKroll]

WARNING: doing an update to your live data may be, shall we say, DANGEROUS. Backup the data and make a duplicate table on which to perform these operations.

UPDATE Table SET ID=ID+1 WHERE ID >= X;

(where X is the number of the first ID that needs to get &quot;bumped&quot;)

I've not run this: it's taken from the mysql docs. But it doesn't specifically say that the WHERE is independant of the updates, i.e., will the ID=ID+1 cause some sort of hideous loop... Backup tables are imporant. :)

[Justin]

Personally, I would make a backup of that table first, and follow these steps *on the backup table*:

1) Remove the &quot;Primary Key&quot; property from the ID column
2) Run Paul's example
3) Insert your new record in its proper position
4) Re-add the primary key property (and auto_increment etc)

Then, after checking that everything is in order, replace the old table with the fixed one.

Some notes:

- The reason I say to remove the 'Primary Key' first is that I'm not positive it would necessarily do the updates in reverse order. Any other order would result in a conflict on the primary key (it wouldn't end up in a loop situation though, MySQL does have protections against that).

- You will also have to update any tables which join with that ID column. That would probably be as simple as Paul's example, substituting the table and the name of the column holding the ID... but again, do it on a backup first and test it.

- One should *never* rely on an auto_increment field for ordering. My suggestion would be to use a date/time stamp, which would default to the date of entry, but perhaps allowing you to override it (or you could change it manually), and ordering on that column. An auto_increment field is never guaranteed to be any particular value (different table types may or may not re-use IDs, etc), they are only guaranteed to be unique...

Hope this helps.
[nbsp]
------------------
Justin Nelson
SFE Software (http://www.sfesoftware.com)

[Arthur]

UPDATE Table SET ID=ID+1 WHERE ID >= X; If the ID field is the primary key, this won't work. E.g. If you bump ID 4 to 5, you'll get two times ID=5, which isn't allowed.
You'd have to add [highest ID-X+1] to every ID>=X (thereby creating new unique indices), insert the new record and then subtract [highest ID-X] for every ID>X.

This is also untested, so make a backup!

[PaulKroll]

Hmmm... wonder if using ORDER BY ID DESC on that would force the order in which the replacements were done, avoiding the primary key problem...

Whatever ya do Jimbo, please tell us how it turned out. (and make backups!) :)

[Rich]

One should *never* rely on an auto_increment field for ordering.
I agree with Justin. A unique ID field should never be relied on for anything other than to keep the items in the table, er, unique.

A better approach than trying to manipulate this field, would be to introduce a new field, say, called GameOrder, and sequence this anyway you want. Then, instead of 'order by gameid' you can do a 'order by GameOrder'. The GameOrder field can be a date or simply default to the 'gameid', if you wish. Then, whenever you need to re-order the entries you can just manipulate the GameOrder entries.

Rich

[jimbo]

Thanks for your replies everyone :).

I will try them all tomorrow when I'm at work, and I'll be sure to give updates :).

Justin,
That is the method that Paul DuBois recommends in his MySQL book - I tried this with a table w/ 3 records in it, and I got all sorts of errors.[nbsp][nbsp]But this was with PhpMyAdmin, I should try it through Telnet.

Rich,

There are 162 games, it's not that easy to reorder them :)

-jim

[PaulKroll]

FYI, it appears ORDER BY doesn't work in current MySQL UPDATEs... I certainly didn't have any luck and the comments on the MySQL docs page for UPDATE weren't encouraging.

There are 162 games, it's not that easy to reorder them
Well that's the point: anyhow, nothing says you have to make such a number sequential: you could make a GameOrder number skip by, say, 10, so if you have nine other games that need to get inserted later, you don't have to alter the whole table to do it. If you expect (or better, have experienced) very few issues like this, use 4 or 5... there's a lot of room in, say, a 32 bit unsigned INT.

Course, if this is what I >THINK< it is on your site (the schedule seems the obvious choice...) then hey, you already HAVE dates for all those critters, and times too. Just sort by those! :)

[jimbo]

Course, if this is what I >THINK< it is on your site (the schedule seems the obvious choice...) then hey, you already HAVE dates for all those critters, and times too. Just sort by those!
Looking back, I don't know why I used the dating scheme that I did.[nbsp][nbsp]But I have every part of the date in a separate column (ie game_day, game_month, game_date).[nbsp][nbsp]Looking back, this was a hideous way to do it, and ended up making my life harder in the long run.[nbsp][nbsp]I'll definitly be doing it this way next season.

-jim

PS:[nbsp][nbsp]trying all of your suggestions right now

[jimbo]

I guess my problem was that I didn't remove the auto_inc &amp; primary key when I tried this Yesterday.

It worked fine, and I thank all of you for your help :).

-jim