SQLite delete returning more than one record I want to limit to just deleting the the first record found

Question

I am using SQLite 3.15 and Java 8.

String createTableQueryPK = "CREATE TABLE IF NOT EXISTS fileBlock (id not null,filename not null, PRIMARY KEY(id,filename))";

My delete function

    public void deleteRecord(String filename) {
    String deleteQuery = "delete from fileBlock where filename=(select * from fileBlock where filename=? order by id limit 1 offset 1)";
            //"delete from fileBlock where filename= (select id, filename from fileBlock where filename=? order by id LIMIT 1);";
    try {
        if(c != null) {
            PreparedStatement prep  = c.prepareStatement(deleteQuery);
            //prep.setInt(1, id);
            prep.setString(1, filename);
            prep.execute();
            System.out.println("record deleted ");
        }
    } catch(Exception e) {
        System.out.println("Error " + e.getMessage());

Gets the following error: Error [SQLITE_ERROR] SQL error or missing database (sub-select returns 2 columns - expected 1) I have tried the following queries any ideas:

delete from fileBlock where filename in (select Distinct filename from fileBlock where filename='test9.txt')

This deletes all records

The idea of the application is that many files coming from different sources some with the same file name or the same user tring to load the same file

How the application will work submit a file name to the delete function and if it is there take the first record there and remove it. Yes I know not a good design

The problem is: How do I delete the first record found in the ressult set and leave all the others intact

Answer 1

If you want to delete just a single 'random' row that matches the given filename, you'll have to use something unique about that row, such as the rowid.

The idea is this (adjust to your database schema as needed):

create table t(filename);
insert into t values('One'),('One'),('One'),('One'),
                    ('Two'),('Two'),('Two'),('Two'),
                    ('Three'),('Three'),('Three');
select count(*) from t;  --initially there are 11 rows in this example

delete from t where rowid = (select rowid from t where filename = 'Two' limit 1);

select count(*) from t; -- now, only 10 rows indicating only one row was deleted

(Even without the limit 1 it will work because of the = matching only the first value returned, but to be more precise.)