CODEWITHSUNDEEP
iossqlsqlite
Jasper Blues
Jasper Blues

Reputation: 28776

Sqlite : How many parameters can there be in an 'in' clause

I'd like to perform the following: 

delete from images where image_address not in (<a long list>)

How long can this list be? (I'm guessing I might have to think of another way).

Upvotes: 2

Views: 1487

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

If you have a long list, I would suggest two approaches:

First solution:

Add all data to temporary table:

CREATE TEMP TABLE lng_list(image_address);
-- Insert all you elements in lng_list table
-- ...
DELETE FROM images WHERE image_address NOT IN (SELECT image_address FROM lng_list);

Make sure to use this inside transaction to get good performace.

Second solution:

(REMOVED: only works for IN, not NOT IN...)

Performance should be fair good for any of those solutions.

Upvotes: 1

CL.
CL.

Reputation: 180162

If you are using parameters (?), the maximum number is 999 by default.

If you are creating the SQL statement dynamically by inserting the values directly (which is a bad thing to do for strings), there is no upper limit on the lenght of such a list. However, there is a limit on the length of the entire SQL statement, which is one million bytes by default.

If you cannot guarantee that your query does not exceed these limits, you must use a temporary table (see LS_dev's answer).

Upvotes: 1

Related Questions