Update randomly selected row in SQLite

Question

I have a table and in it a certain value - by default it is set to -1, but I want to change it to 0 for a random row.

What is the right query for this operation?

Here is what I tried:

UPDATE statuses SET status = 0
WHERE word_id = (
      SELECT word_id FROM statuses WHERE status = -1 ORDER BY RANDOM() LIMIT 1
)

Answer 1

Hmm, I just made a sample table and your query seems to work as written:

sqlite> create table statuses (word_id, status default -1);
sqlite> insert into statuses (word_id) values (1);
sqlite> insert into statuses (word_id) values (2);
sqlite> insert into statuses (word_id) values (3);
sqlite> insert into statuses (word_id) values (4);
sqlite> insert into statuses (word_id) values (5);
sqlite> select * from statuses;
1|-1
2|-1
3|-1
4|-1
5|-1
sqlite> UPDATE statuses SET status = 0
   ...> WHERE word_id = (
   ...>       SELECT word_id FROM statuses WHERE status = -1 ORDER BY RANDOM() LIMIT 1
   ...> );
sqlite> select * from statuses;
1|-1
2|-1
3|0
4|-1
5|-1

So, in other words, your query is right - your error is probably elsewhere in your code.

Answer 2

Does http://www.mail-archive.com/[email protected]/msg14657.html help?