Thanks for tip about the redundant index. How to find which keys have been updated from this query?
On Thu, Jan 24, 2013 at 3:32 PM, Keith Medcalf <kmedc...@dessus.com> wrote: >> I have a table like this: >> >> CREATE TABLE queue ( >> key TEXT NOT NULL PRIMARY KEY UNIQUE, >> status INTEGER >> ); >> CREATE INDEX IF NOT EXISTS keys ON queue (key); > > Your index is redundant. There is already a unique index on key since it is > a primary key. > > It should probably be: > > create unique index if not exists keys on queue(status, key); > >> And then I process it like this, N keys at a time: >> >> SELECT key FROM queue WHERE status=0 LIMIT N; >> BEGIN TRANSACTION; >> for key in keys: >> UPDATE queue SET status=1 WHERE key=key; >> END TRANSACTION; >> >> How can this SELECT and UPDATE be combined more efficiently? > > update queue set status=1 where key in (select key from queue where status=0 > limit N); > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users