更新SQLite中随机选择的行

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.

我有一个表,并在其中有一个特定的值 - 默认情况下它设置为-1,但我想将其更改为0为随机行。

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
)

1 个解决方案

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

换句话说,您的查询是正确的 - 您的错误可能在您的代码中的其他位置。

#1