Results 1 to 4 of 4

Thread: Sqlite delete from a table with primary key

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Sqlite delete from a table with primary key

    Hello
    My table sqlite has 8 records.
    When I delete the first record then I add a new record , the new record takes an ID = 9.
    Is there a way that the new added record takes the ID of the deleted one.
    I'm using Sqlite3 and RC6
    thank you for any idea

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Sqlite delete from a table with primary key

    Re-using the ID numbers is generally not a good idea, as explained in this FAQ article: Database - Why don't AutoNumber/Identity/etc re-use deleted numbers?

    If after reading that you still want to do it, show us how you are adding a new record.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: Sqlite delete from a table with primary key

    Thank you Moderator
    If after reading that you still want to do it, show us how you are adding a new record.
    I still want to do it.
    Code:
    SQL = "Select * from Item_tbl Where item_name =? "
           Set Rs = Cnn.OpenRecordset(SQL)
     With Cnn.CreateSelectCommand(SQL)
         .SetText 1, Text1.Text
         Set Rs = .Execute
       End With
        If Rs.RecordCount = 0 Then Rs.AddNew
          Rs("item_name").Value = Text1.Text
          Rs.UpdateBatch

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sqlite delete from a table with primary key

    Only possible with direct INSERT-Statement

    EDIT: Why deleting at all? Why not just UPDATE the existing Record?
    Last edited by Zvoni; Mar 28th, 2023 at 01:21 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width