CODEWITHSUNDEEP
androidsqlite
user1868569
user1868569

Reputation: 23

Sqlite composite key not in

I have a link table event_user with a composite key based on an user_id and event_id.

I have an array of (user_id, event_id) pairs in Java where i want to ensure that there doesn't exists any other (user_id, event_id) pair in the table that doesn't exists in the array.

In other tables, i just created a string of ids, and then i created the following query

DELETE FROM tablename WHERE column NOT IN ( 1 , 2 , ... n)

However this behavior with NOT IN can not be achieved with multiple columns. How can achieve that using Java and sqlite efficiently?

Thanks in advance.

Upvotes: 1

Views: 115

Answers (1)

CL.
CL.

Reputation: 180200

In SQLite, IN works only with a single column. So you have to write out all comparisons explicitly:

DELETE FROM TableName
WHERE (Col1 != 1 OR Col2 != 10)
  AND (Col1 != 2 OR Col2 != 20)
  AND ...;

SQLite can build an index for a large IN list, but this is not done for large expressions like this. So if the query becomes too big, you can put the IDs into a temporary table, and use a subquery to check for matching rows:

DELETE FROM TableName
WHERE NOT EXISTS (SELECT 1
                  FROM IDsToKeep
                  WHERE IDsToKeep.Col1 = TableName.Col1
                    AND IDsToKeep.Col2 = TableName.Col2);

For efficiency, the temporary table should be indexed (or just be a WITHOUT ROWID table with a PK on both columns).

Upvotes: 1

Related Questions