Row Decrement

[Phantek]

I'm pretty new to using mySQL so please excuse my ineptitude.

I have created an SQL table using phpMyAdmin. I am successfully able to create and delete rows through php scripts. In my table, the first field is "id" which I set as auto_increment. Simply, I want this field to list a number for each row, incrementing for each additional row.

My problem is when I want to delete a row. Although I can delete a row successfully, I am now stuck with a disordered incrementation struction. For example, I have rows 1,2,3,4,and 5. I delete row 3. Now I have 1,2,4,5. I want to be able to decrement each row id after the one that is deleted so I'd end up with 1,2,3,4. Anyone know how I can do this? Thanks.

[teknomage1]

you have to drop the table; and re-enter your data. There's no performance advantage, to having contiguous rows either. If you want to reset the numbering to the current max though you can use

ALTER TABLE name_of_table AUTO_INCREMENT = 1;

[Phantek]

There's really no way to just create a loop to decrement the id value of each row after the one that is deleted?

[teknomage1]

Nope sorry. It's really not that big a deal though. If you only delete from the top, then run that alter statement after every deleton and it won't skip numbers.

[DJMaze]

UPDATE table_name SET id=id-1 WHERE id > 3 ORDER BY id

It's trivial though AND the next entry doesn't have 5 it will have 6 since the auto_increment is active.
ALSO the ORDER BY only works in MySQL 4.x to my knowledge.

[Phantek]

Thanks for your help!

[technobard]

While you can work out a way (and it sounds like you have) to keep the ids in sequence, there are a couple of problems.

1) The update will take longer and longer as your table grows. For large tables, you'll have to figure out an alternative to deal with the slow response times.

2) More importantly, why do you want to do this? As an ID column, my assumption is that this is the primary key. In an ideal world, the value of this column is meaningless. The only thing that matters is that the value is unique. As soon as you assign meaning to the column, you've created an administrative problem.

If possible, find another way to address "why" you're doing this.

Just something to think about.