CODEWITHSUNDEEP
javasqlite
jonderry
jonderry

Reputation: 23633

Delete first k entries from table in java sqlite

I'm trying to expire old entries from my sqlite db using the following code (which is scala, but closely analogous to java code):

  val c = DriverManager.getConnection(
    s"jdbc:sqlite:my_data.db")
  c.setAutoCommit(false)

  val deleteQuery = c.prepareStatement(
    """delete from my_table
      | where id != ?
      | order by id
      | limit ?
      | """.stripMargin)
  deleteQuery.setInt(1, specialId)
  deleteQuery.setInt(2, maxNumRecords / 2)
  logger.info(s"executing delete query: $deleteQuery")
  val dr = deleteQuery.executeUpdate()
  deleteQuery.close()
  logger.info(s"finished executing delete query: $dr $deleteQuery")

  logger.info("committing db queries")
  c.commit()
  logger.info("finished committing db queries")
  c.close()

However, I receive the following exception at the call to prepareStatement:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "order": syntax error)
    at org.sqlite.core.DB.newSQLException(DB.java:890)
    at org.sqlite.core.DB.newSQLException(DB.java:901)
    at org.sqlite.core.DB.throwex(DB.java:868)
    at org.sqlite.core.NativeDB.prepare(Native Method)
    at org.sqlite.core.DB.prepare(DB.java:211)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:40)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:29)
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:18)
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:47)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:251)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:223)

Maybe I'm missing something simple, but running this in the sqlite3 command line seems to work:

sqlite> select id from my_table limit 20;
1
2
3
...
sqlite> delete from my_table where id != 2 order by id limit 5;
sqlite> select id from my_table limit 20;
2
6
7
...

Does anyone know what might be the problem?

Upvotes: 0

Views: 203

Answers (1)

CL.
CL.

Reputation: 180060

ORDER BY for DELETE is not enabled by default.

You can always determine the rows to delete with a subquery:

DELETE FROM my_table
WHERE id IN (SELECT id
             FROM my_table
             WHERE id != ?
             ORDER BY id
             LIMIT ?)

Upvotes: 1

Related Questions