How can I delete a Record from a SQLIte Data Base without Unique ID ?

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.

Instead of

DELETE * FROM tbl WHERE id = 123

you need to put all fields into the where clause:

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.

To expand on Eli’s reply try:

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

SELECT rowid, * FROM TableName

first and change the first statement to have a rowid that is the first number.

Thanks for both answers.

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).

Add a constraint to the table to prevent it happening in future

add a primary key to that table, it won’t waste much space, and will solve the problem in the future.

Or just check if the data already exists before adding new data.

This might work, assuming six records in total.

DELETE FROM TableName LIMIT 5

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 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).

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.

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 :wink:

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.

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

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:

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.

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

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 !).

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.

ALTER TABLE table_name ADD COLUMN id PRIMARY KEY INTEGER AUTOINCREMENT;

Thank you David.

I used that (except the AUTOINCREMENT) and it does not works. SQLite.com documentation states here :

[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).