Search code examples
sqlitedelete-rowsql-delete

sqlite delete row count

I need to write a SQLlite query that will delete rows from a table above 200. I was thinking this would work:

DELETE FROM [tbl_names] WHERE count(*) > 200

but that gives me: misuse of aggregate function count()

I know there is a limit clause I can use, but if I use:

DELETE FROM [tbl_names] LIMIT 200

that looks like it will delete the first 200 rows.

Solution

  • All rows in an SQLite have rowid field, which you can use to find rows greater than 200. For example:

    DELETE FROM [tbl_names] WHERE rowid > 200

    You could also use an offset with your limit:

    DELETE FROM [tbl_names] LIMIT 10000 offset 200

    using the roqid seems to be the better choice.