SQL - renumbering a sequential column to be sequential again after deletion

Question

I've researched and realize I have a unique situation.

First off, I am not allowed to post images yet to the board since I'm a new user, so see appropriate links below

I have multiple tables where a column (not always the identifier column) is sequentially numbered and shouldn't have any breaks in the numbering. My goal is to make sure this stays true.

Down and Dirty We have an 'Event' table where we randomly select a percentage of the rows and insert the rows into table 'Results'. The "ID" column from the 'Results' is passed to a bunch of delete queries.

This more or less ensures that there are missing rows in several tables.

My problem: Figuring out an sql query that will renumber the column I specify. I prefer to not drop the column.

Example delete query:

delete ItemVoid
from ItemTicket
join ItemVoid
on ItemTicket.item_ticket_id = itemvoid.item_ticket_id
where itemticket.ID in (select ID
            from results)

Example Tables Before:

Example Tables After:

As you can see 2 rows were delete from both tables based on the ID column. So now I gotta figure out how to renumber the item_ticket_id and the item_void_id columns where the the higher number decreases to the missing value, and the next highest one decreases, etc. Problem #2, if the item_ticket_id changes in order to be sequential in ItemTickets, then it has to update that change in ItemVoid's item_ticket_id.

I appreciate any advice you can give on this.

Answer 1

(answering an old question as it's the first search result when I was looking this up)
(MS T-SQL)

To resequence an ID column (not an Identity one) that has gaps, can be performed using only a simple CTE with a row_number() to generate a new sequence. The UPDATE works via the CTE 'virtual table' without any extra problems, actually updating the underlying original table.
Don't worry about the ID fields clashing during the update, if you wonder what happens when ID's are set that already exist, it doesn't suffer that problem - the original sequence is changed to the new sequence in one go.

WITH NewSequence AS
(
  SELECT
    ID, 
    ROW_NUMBER() OVER (ORDER BY ID) as ID_New
  FROM YourTable
)
UPDATE NewSequence  SET ID = ID_New;

Answer 2

Since you are looking for advice on this, my advice is you need to redesign this as I see a big flaw in your design.

Instead of deleting the records and then going through the hassle of renumbering the remaining records, use a bit flag that will mark the records as Inactive. Then when you are querying the records, just include a WHERE clause to only include the records are that active:

SELECT *
FROM yourTable
WHERE Inactive = 0

Then you never have to worry about re-numbering the records. This also gives you the ability to go back and see the records that would have been deleted and you do not lose the history.

If you really want to delete the records and renumber them then you can perform this task the following way:

1. create a new table
2. Insert your original data into your new table using the new numbers
3. drop your old table
4. rename your new table with the corrected numbers

As you can see there would be a lot of steps involved in re-numbering the records. You are creating much more work this way when you could just perform an UPDATE of the bit flag.

You would change your DELETE query to something similar to this:

UPDATE ItemVoid
SET InActive = 1
FROM ItemVoid
JOIN ItemTicket
    on ItemVoid.item_ticket_id = ItemTicket.item_ticket_id
WHERE ItemTicket.ID IN (select ID from results)

The bit flag is much easier and that would be the method that I would recommend.