CODEWITHSUNDEEP
sql-servert-sqlsql-updatesequencesql-delete
RiqueW
RiqueW

Reputation: 242

Eliminate sequence-numbering 'gaps' when deleting in SQL Server

I have two tables in a SQL Server database, that are in a many-to-many relationship, with the rows in TableA representing the 'container' structure in my business logic, and TableB the 'child' objects that can be included in any number of those containers. I've created a linking table, TableA_X_TableB, that consists of the columns:

TableA_PK UNIQUEIDENTIFIER, TableB_PK INT, Sequence INT

… with the last column used to record the sequence of TableB items within the TableA 'containers' — because these do, in fact need to be ordered lists.

All of my CRUD is very straightforward, except for this: when I delete an item out of the middle of a list, I would like SQL Server to 'seal the gap' in the sequence numbers that pertain to a particular TableA sequence. I.e., if I have six entries associated with a particular TableA_PK…

TableA_PK                            | TableB_PK | Sequence |
=============================================================
AD7D5099-A14D-48D4-9860-6578EDF7C006 |     10389 |        0 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |      9368 |        1 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |      9537 |        2 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |     18499 |        3 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |     15759 |        4 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |      5872 |        5 |

… and execute:

DELETE TableA_X_TableB
WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006'
AND TableB_PK = 9537

… I would like the Sequence values to read 0 1 2 3 4, and not 0 1 3 4 5.

I've tried numerous approaches to this. I won't list them all, but as an example of something that seemed most likely after a lot of failed experiments (and which also fails, to be clear!):

DECLARE @seq INT
SET @seq = -1;
WITH listEntries AS  (
SELECT TOP 100 PERCENT *
FROM TableA_X_TableB
WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006'
ORDER BY Sequence ASC)
UPDATE listEntries
SET @seq = listEntries.Sequence = @seq + 1
FROM listEntries;

This puts in an updated, numerically correct list of Sequence numbers, alright… but based on the internal database order, not the sort on the previous sequence number. The upshot is that users' carefully sequenced lists become jumbled the moment an item is eliminated.

I can think of a couple of workarounds for this, but

Thanks!

UPDATE =======================================================

A piece of the puzzle was that this table also had a couple of indexes; one to ensure that any combination of TableA_PK and TableB_PK was unique, and a couple of others to speed up some joins across a dataset of significant size. Without the indexes, my sample code above worked fine, but my naive ORDER BY clause would always be overridden by the them once they were in place. (There might have been a solution here, but the accepted one is far more elegant.)

Also, as I suspected, there are certain edge cases in the overall requirement in which sequence gaps are permissible — but only if, and only where, the end user says so. There's no way that I can see in which a straight-ahead ROW_NUMBER()-based solution could resolve that; @jpw's clever and concise 'recipe' below not only solves the original problem, but with a bit of tweaking also permits local resequencing where that becomes necessary.

Upvotes: 2

Views: 1790

Answers (1)

jpw
jpw

Reputation: 44891

Maybe using row_number() partitioned over TableA_PK ordered by sequence like this would work:

UPDATE Table1 SET Sequence = rn
FROM Table1 
INNER JOIN (
    SELECT 
       [TableA_PK], 
       [TableB_PK] , 
       rn= ROW_NUMBER() OVER (PARTITION BY tablea_pk ORDER BY tablea_pk, sequence) -1 
    FROM Table1 
    WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006' 
) derived ON table1.TableA_PK = derived.TableA_PK and Table1.TableB_PK = derived.TableB_PK

Sample SQL Fiddle showing before and after delete and update

Upvotes: 1

Related Questions