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

Question

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.

What exactly is the expected behavior of the query? What are the other suggestions that you have tried? — Steve Konves
– Steve Konves, Commented May 29, 2013 at 15:33
There is no variable in that query. Do you mean that "Text" is different for different rows? — CL.
– CL., Commented May 29, 2013 at 15:37
Do you have any indexes? Why are you using LIKE instead of =? — CL.
– CL., Commented May 29, 2013 at 15:37
I should have said varying text, as opposed to Variable, sorry. Yes the "Text is different for each row. — KielCC
– KielCC, Commented May 29, 2013 at 15:57
I was using LIKE from another query I had which was to update another field based on another value, where LIKE was appropriate. The query in its context is UPDATE Aircraft SET Interested="1" WHERE Registration LIKE "ZK-OKN"; and so on for different Registrations. — KielCC
– KielCC, Commented May 29, 2013 at 15:59

CL. · Accepted Answer · 2013-05-30 06:18:32Z

1

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.

edited May 30, 2013 at 6:18

answered May 29, 2013 at 17:18

CL.

182k18 gold badges241 silver badges282 bronze badges

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

2 Comments

KielCC Over a year ago

Thanks, I will give that a go. As for the update query, should I keep that the same or is there an improved way of doing that?

CL. Over a year ago

If you try to update multiple records with one statement, it's more likely that SQLite will do a full table scan.

Collectives™ on Stack Overflow

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

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