On Wed, 23 Jan 2013 21:32:20 -0700
"Keith Medcalf" <kmedc...@dessus.com> wrote:
> > 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);
Why process only N at a time, Richard? If you remove that requirement
the query is much simpler, because "key" is unique:
update queue set status=1 where status = 0;
Besides being nonstandard, the results using "limit N" are
nondeterministic. You don't know which rows are updated, only that (up
to) N are.
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
