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

sql

Solution

(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;

Problem

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.

Original source