SQLite 删除返回多个记录，我想限制为仅删除找到的第一条记录

Question

我正在使用SQLite 3.15和Java 8。

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

我的删除功能

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());

获取以下错误：错误 [SQLITE_ERROR] SQL 错误或缺少数据库(子选择返回 2 列 - 预期为 1( 我已经尝试了以下查询任何想法：

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

这将删除所有记录

该应用程序的想法是来自不同来源的许多文件，其中一些具有相同的文件名或相同的用户试图加载相同的文件

应用程序将如何工作 向删除功能提交文件名，如果存在，则在此处获取第一条记录并将其删除。 是的，我知道不是一个好的设计

问题是： 如何删除在 ressult 集中找到的第一条记录并保留所有其他记录

Answer 1

如果您只想删除一行，则与哪一行无关紧要。

添加限制 1 以删除查询 例：

delete from fileBlock where filename=(select filename from fileBlock where filename=? order by id limit 1 offset 1)
limit 1;

或 如果需要，可以直接执行删除查询，而无需选择语句。

delete from fileBlock where filename=? limit 1;

Answer 2

如果您只想删除与给定文件名匹配的单个"随机"行，则必须使用该行的独特内容，例如rowid。

这个想法是这样的(根据需要调整到您的数据库架构(：

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

(即使没有limit 1它也可以工作，因为=只匹配返回的第一个值，但更准确地说。

Answer 3

更改此查询以返回文件名而不是*

delete from fileBlock where filename=(select * from fileBlock where filename=? order by id limit 1 offset 1)

自

delete from fileBlock where filename=(select filename from fileBlock where filename=? order by id limit 1 offset 1)

Answer 4

基于以下答案的我的解决方案

public void deleteRecord(String filename) {
String deleteQuery = "delete from fileBlock where rowid = (select rowid from fileBlock where filename = ? limit 1);";
String deleteQuery1 = "delete from fileBlock where filename=(select filename from fileBlock where filename=? order by id limit 1 offset 1)limit 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(deleteQuery1);
//prep.setInt(1, id);
prep.setString(1, filename);
prep.execute();
System.out.println("record deleted ");
}
} catch(Exception e) {
System.out.println("Error " + e.getMessage());
}
}

上面的查询都有效 谢谢