在SQL中更新n个随机行 [英] Update n random rows in SQL

问题描述

我有大约1000行的表.我必须将n个随机行的表中的column("X")更新为'Y'.为此，我可以进行以下查询

I have table which is having about 1000 rows.I have to update a column("X") in the table to 'Y' for n ramdom rows. For this i can have following query

update xyz set X='Y' when m in (
'SELECT m FROM (SELECT m
FROM xyz
order by dbms_random.value
) RNDM 
where rownum < n+1);

还有另一种有效的方式来编写此查询.该表没有索引. 请帮忙吗?

Is there another efficient way to write this query. The table has no index. Please help?

推荐答案

我将使用ROWID:

UPDATE xyz SET x='Y' WHERE rowid IN (
    SELECT r FROM (
        SELECT ROWID r FROM xyz ORDER BY dbms_random.value
    ) RNDM WHERE rownum < n+1
)

虽然我使用ROWID的实际原因不是为了提高效率(它仍然会进行全表扫描)-如果列m不是唯一的，您的SQL可能不会更新您想要的行数.

The actual reason I would use ROWID isn't for efficiency though (it will still do a full table scan) - your SQL may not update the number of rows you want if column m isn't unique.

只有1000行，您不必担心效率(也许有一亿行).该表上没有任何索引，您将无法进行全表扫描以选择随机记录.

With only 1000 rows, you shouldn't really be worried about efficiency (maybe with a hundred million rows). Without any index on this table, you're stuck doing a full table scan to select random records.

但是，如果有100,000行呢?"

嗯，这仍然比1亿个少了三个数量级.

Well, that's still 3 orders of magnitude less than 100 million.

我运行了以下内容:

create table xyz as select * from all_objects;

[在我的系统上创建了大约50,000行-未索引，就像您的表一样.

[created about 50,000 rows on my system - non-indexed, just like your table]

UPDATE xyz SET owner='Y' WHERE rowid IN (
     SELECT r FROM (
          SELECT ROWID r FROM xyz ORDER BY dbms_random.value
     ) RNDM WHERE rownum < 10000
);
commit;

这花费了大约1.5秒.也许是1秒，也许是3秒(不是正式计时，它只是花了足够的时间闪烁).

This took approximately 1.5 seconds. Maybe it was 1 second, maybe up to 3 seconds (didn't formally time it, it just took about enough time to blink).

这篇关于在SQL中更新n个随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！