Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Deleting rows from SQLite database leave gaps.
Forum Updated to NodeBB v4.3 + New Features

Deleting rows from SQLite database leave gaps.

Scheduled Pinned Locked Moved Solved General and Desktop
7 Posts 3 Posters 3.0k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • A Offline
    A Offline
    andrewhopps
    wrote on last edited by andrewhopps
    #1

    I've got all functions set to retrieve and display data from my database, but removing a single entry forever ruins any and all further displaying of information. The rows removed leave a gap in the database, such as: 1,2,3,5,6,etc. How do I remove the rows in the database and preserve sequential numbering of rows with no gaps?

    Noob friendly responses would be appreciated.

    1 Reply Last reply
    0
    • EatonCodeE Offline
      EatonCodeE Offline
      EatonCode
      wrote on last edited by
      #2

      Just between 1 noob to another...

      If your using keys and you delete from the database.

      1, John, Doe
      2, Robert, Alexander
      3, Man, Bat
      4, Simpson, Homer
      5, Eaton, David
      6, Par, Robert

      And you do something like this...

      delete from mydata where key=4

      Your going to be left with.

      1, John, Doe
      2, Robert, Alexander
      3, Man, Bat
      5, Eaton, David
      6, Par, Robert

      Your Next Index will still be 7

      You could reindex your key values.. but if you reference anything from the keys, it will not have the correct information anymore.

      Look at the answer here.

      http://stackoverflow.com/questions/13644577/how-to-re-index-number-sorted-sqlite-table

      Hope this helps.

      A 1 Reply Last reply
      0
      • EatonCodeE EatonCode

        Just between 1 noob to another...

        If your using keys and you delete from the database.

        1, John, Doe
        2, Robert, Alexander
        3, Man, Bat
        4, Simpson, Homer
        5, Eaton, David
        6, Par, Robert

        And you do something like this...

        delete from mydata where key=4

        Your going to be left with.

        1, John, Doe
        2, Robert, Alexander
        3, Man, Bat
        5, Eaton, David
        6, Par, Robert

        Your Next Index will still be 7

        You could reindex your key values.. but if you reference anything from the keys, it will not have the correct information anymore.

        Look at the answer here.

        http://stackoverflow.com/questions/13644577/how-to-re-index-number-sorted-sqlite-table

        Hope this helps.

        A Offline
        A Offline
        andrewhopps
        wrote on last edited by
        #3

        @EatonCode

        just leaving the answer as found, I'm gathering that you're setting the rowid to their current index in the database with this?

         UPDATE todo SET id = rowid
        
        
        EatonCodeE 1 Reply Last reply
        0
        • A andrewhopps

          @EatonCode

          just leaving the answer as found, I'm gathering that you're setting the rowid to their current index in the database with this?

           UPDATE todo SET id = rowid
          
          
          EatonCodeE Offline
          EatonCodeE Offline
          EatonCode
          wrote on last edited by
          #4

          @andrewhopps I was referring to this... http://stackoverflow.com/a/13644816

          Not really sure what your going for but, from my own experience in my past database design I made this same mistake....

          Now with some experience under my belt I would never re-index unless I had thousands of missing numbers and I never referenced the key some else in the database.

          using the example above..

          Select key from mydata where key = 6 Expecting Par, Robert.

          If you re-indexed key 6 may not exist. Or worse a wrong name......

          Hope it works out. :)

          A 1 Reply Last reply
          0
          • EatonCodeE EatonCode

            @andrewhopps I was referring to this... http://stackoverflow.com/a/13644816

            Not really sure what your going for but, from my own experience in my past database design I made this same mistake....

            Now with some experience under my belt I would never re-index unless I had thousands of missing numbers and I never referenced the key some else in the database.

            using the example above..

            Select key from mydata where key = 6 Expecting Par, Robert.

            If you re-indexed key 6 may not exist. Or worse a wrong name......

            Hope it works out. :)

            A Offline
            A Offline
            andrewhopps
            wrote on last edited by andrewhopps
            #5

            @EatonCode
            My database, more importantly table is of only 2 columns and displayed in a QTableView and is updated after any change to the table. So the table is always displaying the current tables information. I edit the database by selecting the row in the QTableView and making my changes in text fields and pressing an update button. All fetching from the database is relative so their starting indexes/rowids do not matter. Constantly remaking a table over and over again seems insanely overdone, so forgive my lack of understanding and or clarifying before, but I just need to get things in sequential order in the simplest way possible regardless of how many rows I add or remove. I can add another query to reset it after each edit, I just don't know what I need to write to accomplish that.

            T 1 Reply Last reply
            0
            • A andrewhopps

              @EatonCode
              My database, more importantly table is of only 2 columns and displayed in a QTableView and is updated after any change to the table. So the table is always displaying the current tables information. I edit the database by selecting the row in the QTableView and making my changes in text fields and pressing an update button. All fetching from the database is relative so their starting indexes/rowids do not matter. Constantly remaking a table over and over again seems insanely overdone, so forgive my lack of understanding and or clarifying before, but I just need to get things in sequential order in the simplest way possible regardless of how many rows I add or remove. I can add another query to reset it after each edit, I just don't know what I need to write to accomplish that.

              T Offline
              T Offline
              Tom_H
              wrote on last edited by
              #6

              @andrewhopps Doing a vacuum will rewrite the ROWIDs, but you don't want to do that. Keep a map of rowid to table row number.

              A 1 Reply Last reply
              1
              • T Tom_H

                @andrewhopps Doing a vacuum will rewrite the ROWIDs, but you don't want to do that. Keep a map of rowid to table row number.

                A Offline
                A Offline
                andrewhopps
                wrote on last edited by andrewhopps
                #7

                @Tom_H After messing around and looking further into it, this does 100% what I was looking for. So thank you for the suggestion, even if it wasn't exactly the intent.

                1 Reply Last reply
                0

                • Login

                • Login or register to search.
                • First post
                  Last post
                0
                • Categories
                • Recent
                • Tags
                • Popular
                • Users
                • Groups
                • Search
                • Get Qt Extensions
                • Unsolved