安卓 SQLite : Delete first n records in a table (is deleting all rows instead)

Question

我正在使用以下 sql 语句删除表中的前 5 行:

DELETE FROM tblname WHERE `id`   IN (SELECT `id`   FROM tblname ORDER BY `id`   ASC LIMIT 5)

它不会删除 5 行，而是删除所有行。

我还尝试了以下变体:

DELETE FROM tblname WHERE 'rowid' IN (SELECT 'rowid' FROM tblname ORDER BY 'rowid' ASC LIMIT ?)

我找不到由此产生的任何错误。这是我用来执行此操作的具体代码:

final SQLiteDatabase db = getWritableDatabase();
final String selectQuery = "DELETE FROM " + TABLE_NAME + " WHERE '_id' IN (SELECT '_id' FROM "+ TABLE_NAME + " ORDER BY '_id' ASC LIMIT 5)";

final Cursor cursor = db.rawQuery(selectQuery, null);

cursor.moveToFirst();
cursor.close();

如有任何指点，我们将不胜感激。

Answer 1

您确定“_id”应该用单引号引起来吗？通常，您不会在列名周围加上引号。我用一个名为 id 的整数主键字段创建了一个表，这个查询按预期工作:

DELETE FROM tblname WHERE id IN(SELECT id FROM tblname ORDER BY id ASC LIMIT 1)

但是这个查询删除了所有内容:

DELETE FROM tblname WHERE 'id' IN(SELECT 'id' FROM tblname ORDER BY 'id' ASC LIMIT 1)