Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Angry Unanswered: Query problem: Automatically resetting a sequential number list after row deletion

    Hello!
    Got a problem I hope some clever people can help me out with..

    I have a web form that displays a set of records in a grid. The grid is "paged" according to a PageNum column, with a dropdown box to change pages and buttons allowing the items to be moved up or down a list within a page or moved between pages. So the backend table (simplified) looks something like this

    PageNum ItemNum ItemDescription
    ----------- ----------- -------------------
    1 1 aaaaaaa
    1 2 bbbbbbb
    1 3 cccccccc
    2 1 ddddddd
    2 2 eeeeeee
    2 3 ffffffffffff
    3 1 ggggggg
    3 2 hhhhhhh
    3 3 iiiiiiiiiiiiiiii

    The problem is when I want to delete a page - I need the page numbers to automatically resequence themselves, so for example, If I delete "Page 2" (i.e. delete rows where PageNum = 2), all items on "Page 3" become "Page 2" (and any items on "Page 4" become "Page 3" etc).

    This has proved straightforward to when deleting an item from a particular page, and can resequence ItemNum thanks to a clever bit of code found on SQLteam.com:

    DECLARE @intCounter int
    SET @intCounter = 0
    UPDATE <Item Table>
    @intCounter = ItemNum = @intCounter + 1
    WHERE Pagenumber = <Currently Selected Page>

    However I haven't been able to adapt this to resequence the Page number, as this involves resequencing blocks of numbers. The closest I can get is:

    DECLARE @intCounter int
    SET @intCounter = 1
    UPDATE <Itemtable>
    SET @intCounter = PageNum = CASE
    WHEN @intCounter = PageNum - 1 THEN @intCounter + 1
    WHEN @intCounter = PageNum - 2 THEN @intCounter + 1
    ELSE @intCounter
    END

    But this doesn't quite work.
    Anyone got any other ideas??

    Thanks
    Greg

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't use code like this?:

    Update Itemtable
    set PageNum = PageNum - 1
    where PageNum > DeletedPageNum

    Perhaps a better solution would be to have a table of "PageNums" which contains both the page number and a surrogate key. The Itemtable would contain the surrogate key rather than the PageNum, allowing you to alter the order of the PageNums without having to update the ItemTable at all.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    32
    Ahhh yes...why do something simple when you can make it complicated

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Posts
    32
    Ahhh yes...why do something simple when you can make it complicated

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •