Alexei Alexandrov a écrit : > Hi, > > Basically, I need to do the following: given a big-big table, I need > to iterate through all its rows and change a column in about half the > rows. The contents of the column to change depends on other columns, > but only partially, so triggers won't work here. Well, this mainly depends on your database structure. If the column to change is indexed, a simple UPDATE some_table SET some_column=some_value_or_function_result WHERE some_condition can be quite fast.
Otherwise you should use the EXPLAIN statement on the query to see what happens and is a time consumer. > What would be the fastest way to do it? I assume that you cannot just > do prepare/step and do updates while iterating over the table, right? > Remembering rowid-s for the rows to change can potentially eat all my > (well, computer's) memory, so it doesn't look perfect either. This also depends on what interface you use. Will it be C, C++ or PHP, Perl, Python ? PHP PDO_SQLite interface uses an object oriented syntax which allows pure SQL queries without thinking about prepare/step paradigm. It's very efficient, even on huge tables (more than 10 million records). > This task is solved pretty trivially and efficiently in dbf-based > simple databases (FoxPro, Codebase), but it seems to me that I'm just > missing something very basic about SQLite features. Dbf based databases have different paradigms than pure SQL based ones. When I deal with dbase syntax, I usually now use Clip, a Clipper compatible free system which allows SQL xBase queries.
