Syntax exception while trying to delete a list of objects from the database in Android

Question

Error

android.database.sqlite.SQLiteException: near ",": syntax error (code 1): , while compiling: DELETE FROM messages_table WHERE (time,recepient_id,sender_id) IN (("1469945224502","8c79c7d4bf2c11e38867e83935244df8","0a0897ceed5a11e38867e83935244df8"),("1466672473543","8c79c7d4bf2c11e38867e83935244df8","0a0897ceed5a11e38867e83935244df8"))
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.delete(SQLiteDatabase.java:1496)
at $JUST_MY_CLAS_NAME$6.run(BasicDAO.java:136)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
at java.lang.Thread.run(Thread.java:818)

What can be the cause of it?

It's executed without errors on the server database but fails on Android.

Problem: I need to remove a lot of data from data base. I'm getting data in a form of POJOs list. So I need to create query dynamically. This is just simple case to demonstrate problem.

Probably this isn't the most optimal way, any suggestions and especially documentation will be appreciated.

CL. · Accepted Answer · 2016-07-09 12:28:26Z

2

In SQLite,

the IN and NOT IN operators take a single scalar operand on the left and a vector operand on the right.

So you have to write out the comparisons:

DELETE FROM messages_table
WHERE (time         = '1469945224502'                    AND
       recepient_id = '8c79c7d4bf2c11e38867e83935244df8' AND
       sender_id    = '0a0897ceed5a11e38867e83935244df8')
      OR
      (time         = '1466672473543'                    AND
       recepient_id = '8c79c7d4bf2c11e38867e83935244df8' AND
       sender_id    = '0a0897ceed5a11e38867e83935244df8');

edited Jul 9, 2016 at 12:28

answered Jul 8, 2016 at 10:07

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Юрій Мазуревич Over a year ago

Great, this is looks almost like a perfect answer. Can you please tell me where to read documentation so I have a prove for my boss. And can you please tell me is it quicker to loop through long list and delete each item separately, or form a long queue to database from that list and execute it once. Given that I'll think how to name my question so others can find it. And I'll accept your answer.

CL. Over a year ago

How to optimize this would be a different question.

Collectives™ on Stack Overflow

Syntax exception while trying to delete a list of objects from the database in Android

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related