CODEWITHSUNDEEP
sqlsqlitewhere-clause
KielCC
KielCC

Reputation: 11

SQLite UPDATE multiple rows with a single value based on variable text

First off, apologies if this has been covered in a previous post and I've missed it. I've done a lot of searching and haven't come across anything that has worked so far.

I'm currently using the below query to update records in a table.

UPDATE table SET Column1="1" WHERE Column2 LIKE "Text";

The problem is I have over 100,000 rows, each with different text, to update and even doing 15,000 at a time takes 45 mins. I'm new to SQL, but I'm fairly certain that isn't the most efficient way to update the rows I need to update.

I've been trying to use other suggestions I've come across on here but none have worked so far.

Upvotes: 1

Views: 913

Answers (1)

CL.
CL.

Reputation: 180060

To be able to look up records fast, you need indexes on the relevant columns. (See Query Planning for details.)

In this particular case, you need an index on Column2. LIKE is case-insensitive, so you must create a case-insensitive index, like this:

CREATE INDEX MyTable_Column2 ON MyTable(Column2 COLLATE NOCASE);

Furthermore, Column2 must have been declared with TEXT affinity.

Wrap multiple updates into one transaction, otherwise the transaction overhead will be much larger than the time needed for the actual updates.

You can always check with EXPLAIN QUERY PLAN whether your query is able to use an index.

Upvotes: 1

Related Questions