Reorder ID in table after Delete or Insert

Question

Reorder ID in table after Delete or Insert

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a table with a column named ID . This ID is not Auto Increment nor is it a primary key, but it follows a numeric sequence (eg 1, 2, 3, 4, 5 ...).

I need to include or delete in a variable position, recalculate the IDs and do update . Example:

If DELETE or ID 2, the next IDs subtract 1. Where 3 turns 2 , 4 turns 3 , etc.

The same thing when INCLUDING in a variable position. Any idea how to do it?

php

asked by anonymous 26.08.2014 / 19:10

2 answers

Eloquent + Object loading How to use ping / pong in Java Websocket

score 1 · Answer 1

When something gets crooked late or never right. I would do so: - rearranged the table, left the right IDs. It's not difficult, but it can take a lot of time to spend

select id from table
$n=1;
while($row=fetch)
{
   UPDATE table SET id=$n WHERE id = $row[id];
   $n++;
}

instead of delete, when to take some registry, I would do an update on the line, leave all the columns blank, less the ID that was left with the original value.
When you needed to enter a value, you searched for the lowest available ID. If it was not available create a new line.

Then it gets easier to maintain. If you start having lots of empty lines, fill in these lines with the last entries and delete the last entries.

score 0 · Answer 2

Based on the answer above (or below) that will possibly be deleted or incorporated into the initial topic in which you say you intend to re-create this database one day, I think the reason is more cosmetic.

So, as I also believe you should not have as large a fragmentation as you seem to have today at short intervals of time then, to reorder everything at once, simply delete the primary key column and recreate it.

But it's extremely important that you get used to the fact that your IDs will not always be sequential.