The index on the F1 column is irrelevant -- it might not be used unless it is significantly faster to scan than the table itself. On the other hand it will have to be updated for each row in T1 that is updated where the value of F1 is re-written (not necessarily changed).
The WHERE clause limits the effect of the update such that only rows matching the condition are updated. So, if you use: UPDATE T1 SET f1=replace(f1, 'x', 'y') Then every row will be updated with the new value of F1 (which may be the same as the previous value if it does not contain something to replace). UPDATE T1 SET f1=replace(f1, 'x', 'y') WHERE F1 LIKE '%x%' Will scan the table looking for rows that MAY need updating because they contain an 'x' or and 'X', and then update only that subset of rows, some of which may be updated with the same value as it already contained. So, if the table T1 has 5 rows, there will be no difference little difference. If it has 1 Billion rows, with 1 Million containing 'X' or 'x', then the former (no where clause) will update (read and re-write) all billion rows (and the index). With a where clause it will only scan (read) 1 Billion rows and only update (re-write) 1 Million (and 1 Million index entries). So, you can write it both ways and both are correct. Whether you include a where clause to limit the rows you are updating is a decision you will have to make and might have to take into account such things as how long the update will take. Without the WHERE clause it will take, in the above example, at least 1000 times longer without the WHERE clause. This may or may not be significant in your circumstances. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Gert Van Assche > Sent: Sunday, 24 May, 2015 09:17 > To: General Discussion of SQLite Database > Subject: [sqlite] Update with out without WHERE? > > Hi all, > > When I do an update in a huge table, should it write it like this > UPDATE T1 SET F1=replace(F1, 'x', 'y') WHERE F1 LIKE '%x%'; > or without where clause like this > UPDATE T1 SET F1=replace(F1, 'x', 'y'); > > T1 has an index on F1, which is a TEXT field. > > thanks > > gert > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
