SQL - renumber sequential column to be sequentially after deletion
I researched and realized that I have a unique situation.
Firstly, I am not allowed to post images to the board as I am a new user, so see the relevant links below
I have several tables where columns (not always ID columns) are numbered sequentially and should not have any breaks in the numbering. My goal is to make sure this is true.
Down and Dirty We have an Event table where we randomly select a percentage of rows and insert rows into the Results table. The "ID" column from "Results" is passed to the heap of delete requests.
This more or less ensures that there are no rows in multiple tables.
My problem: Evaluating a sql query that will renumber the specified column. I prefer not to drop the column.
Example of a delete request:
delete ItemVoid
from ItemTicket
join ItemVoid
on ItemTicket.item_ticket_id = itemvoid.item_ticket_id
where itemticket.ID in (select ID
from results)
Examples of tables:
Examples of tables After:
As you can see, two rows have been removed from both tables based on the ID column. So now I have to figure out how to renumber the item_ticket_id and item_void_id columns, where the larger number decreases to the missing value, and the next highest number decreases, etc. Problem # 2, if the item_ticket_id changes to be consistent in the ItemTickets, then it has to update that ItemVoid change to the item_ticket_id.
I appreciate any advice you can give on this.
source to share
(answering an old question as this is the first search result when I searched for this)
(MS T-SQL)
To iterate over an ID (not ID) column having spaces can be done using just a simple CTE with row_number()
to generate a new sequence.
UPDATE
works through the CTE virtual table without any additional problems, actually updating the original table.
Don't worry about ID conflicts during the upgrade, if you are wondering what happens when IDs are set that already exist, this does not suffer from this 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;
source to share
Since you are looking for advice on this matter, I suggest you redo this as I see a big flaw in your design.
Instead of deleting records and then iterating over the numbering of the remaining records, use a flag bit
that will mark the records as Inactive
. Then, when you ask for records, just include the clause WHERE
to include only those records that are active:
SELECT *
FROM yourTable
WHERE Inactive = 0
Then you don't have to worry about re-numbering your records. It also gives you the ability to go back and see records that would have been deleted without losing your history.
If you really want to delete records and renumber them, you can accomplish this task like this:
- create a new table
- Insert original data into new table using new numbers
- delete your old table
- rename the new table with corrected numbers
As you can see, there would be many steps involved in re-numbering the entries. You're doing a lot more work this way when you can just execute the UPDATE
bit flag.
You would change your query DELETE
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 flag is bit
much simpler and this would be the method I would recommend.
source to share
The function you are looking for is the window function. In standard SQL (SQL Server, MySQL) the row_number () function. You use it like this:
select row_number() over (partition by <col>)
from <table>
To use this in your case, you have to delete rows from the table and then use the with statement to recalculate the row numbers and then assign them with an update. For transaction integrity, you can wrap the delete and update in a single transaction.
Oracle supports similar functionality, but the syntax is slightly different. Oracle calls these functions analytic functions, and they support a richer set of operations on them.
I would highly recommend that you use cursors as they have lousy performance. Of course, this will not work on an identity column, since such a column cannot be changed.
source to share