0

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Kris B
  • 3,436
  • 9
  • 64
  • 106
  • For anyone interested, this seems to work: DELETE FROM [tbl_name] WHERE rowId IN (SELECT rowId FROM [tbl_name] ORDER BY rowId LIMIT 1000 OFFSET 200) – Kris B Oct 10 '11 at 01:19

1 Answers1

1

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.

Doug Flower
  • 226
  • 1
  • 2
  • 7
  • My table has an autoincrement integer primary key field and, from what I've read, if you have a primary key defined it will use that for rowid, which won't work for me, since individual rows can be deleted before my query to delete rows above 200 is run. – Kris B Oct 09 '11 at 02:23