Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

> If you want to know which rows were updated regardless of the key, what
> you need is a column to hold a unique value for each update transaction,
> and set it as part of the UPDATE.  You could add a datetime column,
> for example, if the time resolution is fine enough.

Good idea. I found setting the status column to epoch was considerably faster:

epoch = int(datetime.datetime.now().strftime('%s%f'))
conn.execute('UPDATE queue SET status=? WHERE key in (SELECT key FROM
queue WHERE status=? LIMIT ?);', (epoch, 0, limit))
rows = conn.execute('SELECT key FROM queue WHERE status=?', (epoch,))
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

On Fri, Jan 25, 2013 at 1:36 AM, Yongil Jang  wrote:
> There is sqlite3_update_hook() function that returns rowid of changed
> record.

sounds promising, but apparently not supported by the python wrapper.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

> Why process only N at a time, Richard?

There are a number of workers who request unprocessed jobs from the queue.
But the queue is too big to hold in memory all at once.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

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  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