# How can I delete a Record from a SQLIte Data Base without Unique ID ? **URL:** \ **Category:** Databases\ **Created:** [April 23, 2017, 10:38am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287 "2017-04-23T10:38:41Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 10:38am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/1 "2017-04-23T10:38:41Z")
The question says all: In a SQLite data base, I want to remove a Record from a Table who was created without a Unique ID \*. Is it possible ? - It is a service table, not meant to have more than one Record. It actually have 6 Records with the exact same contents… I only want to keep one Record. ---
**Author:** ![Eli\_Ott](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/5f8ce5/32.png) [@Eli\_Ott](https://forum.xojo.com/u/Eli_Ott)\ **Post date:** [April 23, 2017, 11:09am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/2 "2017-04-23T11:09:15Z")
> [@327320:@Emile Schwarz](#): > > I want to remove a Record from a Table who was created without a Unique ID Instead of ```auto DELETE * FROM tbl WHERE id = 123 ``` you need to put all fields into the where clause: ```auto DELETE * FROM tbl WHERE fld1 = 'abc' AND fld2 = 'def' AND fld3 = 123 // if the table has 3 columns ``` In your special case of SQLite: there is an internal primary key, which you can access by the name of ROWID. ---
**Author:** ![Simon\_Berridge](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/cc9497/32.png) [@Simon\_Berridge](https://forum.xojo.com/u/Simon_Berridge)\ **Post date:** [April 23, 2017, 11:26am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/3 "2017-04-23T11:26:29Z")
To expand on Eli’s reply try: ```auto DELETE FROM TableName WHERE rowid <> 1 ``` This assumes that the rowid will start from 1. If you can see the data with a select statement do ```auto SELECT rowid, * FROM TableName ``` first and change the first statement to have a rowid that is the first number. ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 11:38am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/4 "2017-04-23T11:38:48Z")
Thanks for both answers. > [@327321:@Eli Ott](#): > > In your special case of SQLite: there is an internal primary key, which you can access by the name of ROWID. I tried it: no result. > you need to put all fields into the where clause: > All records have the exact same contents… The (temporary) alternative is to delete the table (drop) and recreate it. But in the mid-term, if this reappears, I have to have a solution. THIS HAPPENS ONLY IN MY LOCAL COPY OF THE DATA BASE. (not the client’s one). ---
**Author:** ![James\_Dooley](https://forum.xojo.com/user_avatar/forum.xojo.com/james_dooley/32/164_2.png) [@James\_Dooley](https://forum.xojo.com/u/James_Dooley)\ **Post date:** [April 23, 2017, 11:46am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/5 "2017-04-23T11:46:50Z")
Add a constraint to the table to prevent it happening in future ---
**Author:** ![Jean-Yves\_Pochez](https://forum.xojo.com/user_avatar/forum.xojo.com/jean-yves_pochez/32/21769_2.png) [@Jean-Yves\_Pochez](https://forum.xojo.com/u/Jean-Yves_Pochez)\ **Post date:** [April 23, 2017, 11:48am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/6 "2017-04-23T11:48:23Z")
add a primary key to that table, it won’t waste much space, and will solve the problem in the future. ---
**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\ **Post date:** [April 23, 2017, 11:53am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/7 "2017-04-23T11:53:23Z")
Or just check if the data already exists before adding new data. ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [April 23, 2017, 12:23pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/8 "2017-04-23T12:23:10Z")
This might work, assuming six records in total. ```auto DELETE FROM TableName LIMIT 5 ``` ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 12:56pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/9 "2017-04-23T12:56:54Z")
Kem (Thanks !): I get Syntax Error on both lines. I use 1 because I only need 1 Record. [code]TableName = “Heading” db.SQLExecute(“DELETE FROM " + TableName + " LIMIT 1” db.SQLExecute(“DELETE FROM Heading LIMIT 1”[/code] Heading is the TABLE name. Jean-Yves: Yes, I think I will go that way… Greg: I really do not know how this is possible. I noticed that if I quit the application while the NAG screen is displayed, I get that. I was thinking I resolved the problem when I excluded Quit when the NAG (sorry: Information screen) is displayed. This never came back until I noticed that last week. I may also have to check and recheck my code in the App Event, specifically where I create the TABLES and fill them. Anyway: I have to extract from my copy of the file a Table (Fisrst Name, Family Name, Photo and Unique ID): these are not in the Client computer (my addition for my own use). So I will export the whole DB contents and re-define it / create two files, import the data into their own file. Then I will check how I wrote Record(s) into the two Service Tables - I think I know: I check if TABLE Exists, then I fill it (in all cases…). BTW: I tried to ALTER TABLE, but like the [sqlite.com](http://sqlite.com) documentation says, this does not works in case of ADD Unique ID… The other service TABLE, with also is in excess of Records HAVE an Unique ID and therefore was really easy for me to delete the useless Records (and keep only 1). ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [April 23, 2017, 1:13pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/10 "2017-04-23T13:13:52Z")
You’re missing the closing parenthesis. LIMIT 1 means you only want to delete 1 record, not that you want to be left with 1 record. ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 1:16pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/11 "2017-04-23T13:16:51Z")
Kem: you are right !!! And I do not noticed that ! THANKs … I was right when I take a time to get back my ideas in… For the # of Records to delete: thanks too, but if the delete occured, I will noticed what happens 😉 ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 1:18pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/12 "2017-04-23T13:18:10Z")
> [@327330:@Greg O'Lone](#): > > Or just check if the data already exists before adding new data. Greg: a second reading after a coffee, a break, etc. leads me to understand where to search. Yes, in my “ADD THIS SERVICE TABLE” method, I check in the TABLE already exists (to avoid the duplicate / error), but the Record ADD appears after and so can be executed many times (if a reason exists). I will change that. ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [April 23, 2017, 2:18pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/13 "2017-04-23T14:18:19Z")
> [@327320:@Emile Schwarz](#): > > In a SQLite data base, I want to remove a Record from a Table who was created without a Unique ID In SQLite it WILL have a ROWID (a “hidden field”) unless you specifically created the table with a “WITHOUT ROWID” clause If you specified a field of your own that is PRIMARY KEY INTEGER AUTOINCREMENT, than that Field will map to ROWID and can be accessed by either the name ROWID or the name you gave the Primary Key But by DEFAULT all SQLITE tables have a RowID unless explicitly told not to ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 4:28pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/14 "2017-04-23T16:28:33Z")
Dave: thank you for the explanation. But: how do you know the rowid value for the say 5 current Records of Table\_Name ? Used code: ```auto DB.SQLExecute("DELETE FROM " + Table_Name + " WHERE ROWID=" + Row_ID) ``` Works fine. But you are deleting Record in the dark. (better that than nothing. Thanks all (collective effort). The nap (and the advice) was good. ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [April 23, 2017, 4:31pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/15 "2017-04-23T16:31:43Z")
You don’t - thats why every table SHOULD have a primary key of some kind regardless if its “just a lookup table” etc Otherwise you have to identify the row by column values and you still run the risk that if there are rows with duplicate values that you delete more than one row ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 4:44pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/16 "2017-04-23T16:44:06Z")
Norman: I forgot to add the Unique ID in this TABLE. My fault. That said, it is good to know how to set things right after doing something wrong. At last, this does not allow me to makes the things right at a time in the future. Conclusion: 4 TABLES, 3 with UNIQUE ID: that is 75 % of good work (heah !) and 25 % of bad work (boooooo !). ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 23, 2017, 5:16pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/17 "2017-04-23T17:16:17Z")
Used code: [code] Dim Table\_Name As String Dim Row\_ID As String Dim LoopIdx As Integer ``` Table_Name = "Heading" For LoopIdx = LB_Records.ListCount DownTo 2 Row_ID = Str(LoopIdx) TableSQLite.SQLExecute("DELETE FROM " + Table_Name + " WHERE ROWID=" + Row_ID) If UserCancelled Then Exit Next[/code] ``` LB\_Records is a Listbox reference where I display the TABLE Record Contents. DownTo 2 have the adventage to keep one Record in the Table (exactly what I want). Of course, the code above can be writtent a bit differently. For example, get the Heading TABLE RecordSet Count instead of the ListBox’s ListCount. That value canbe computed before the For …/… Next loop. ---
**Author:** ![David\_Cox](https://forum.xojo.com/user_avatar/forum.xojo.com/david_cox/32/227_2.png) [@David\_Cox](https://forum.xojo.com/u/David_Cox)\ **Post date:** [April 24, 2017, 7:36am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/18 "2017-04-24T07:36:12Z")
```auto ALTER TABLE table_name ADD COLUMN id PRIMARY KEY INTEGER AUTOINCREMENT; ``` ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [April 24, 2017, 8:49am UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/19 "2017-04-24T08:49:39Z")
Thank you David. I used that (except the AUTOINCREMENT) and it does not works. [SQLite.com](http://SQLite.com) documentation states [here](http://sqlite.com/lang_altertable.html) : [code] The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions: ``` The column may not have a PRIMARY KEY or UNIQUE constraint.[/code] ``` I now have code to remove the duplicate(s) Record(s) in the two involved TABLEs / modified the TABLE creation code for the one without Unique ID ( it was a simple mater of an `End If` location (I moved it to the end of my code, so it adds a new Record only if the code creates the Table and that makes sense - I do not saw that while coding). ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [October 29, 2020, 6:44pm UTC](https://forum.xojo.com/t/how-can-i-delete-a-record-from-a-sqlite-data-base-without-unique-id/37287/20 "2020-10-29T18:44:18Z")