如何使用从已知列表中随机选择的字符串更新表中的字段？

I have a table named "buildings" that contains a varchar(50) field named "use". The table already has several thousand records, however the "use" values are all null. I would like to update these this table with randomly chosen values from a list of strings (e.g., warehouse, office, market, retail, workshop). I would also like to leave some of these null to emulate real world usage.

我有一个名为“buildings”的表,其中包含名为“use”的varchar(50)字段。该表已有数千条记录,但“use”值均为null。我想用字符串列表中的随机选择值更新这些表(例如,仓库,办公室,市场,零售,研讨会)。我还想留下一些null来模拟真实世界的用法。

How can I update a field in a table with strings randomly selected from a known list?

如何使用从已知列表中随机选择的字符串更新表中的字段?

2 个解决方案

#1

This might work for you:

这可能对你有用:

BEGIN;
UPDATE Buildings SET Use = (ARRAY['warehouse', 'office', 'market', 'retail', 'workshop', NULL])[floor(random() * 6.0) + 1];
COMMIT;

#2

It's not random, but this is a nice and easy way to do it, provided you have a realtively uniform distribution of IDs:

它不是随机的,但这是一个很好的简单方法,只要您有一个实际均匀的ID分布:

UPDATE Buildings SET Use = 'warehouse' WHERE ID % 6 = 0
UPDATE Buildings SET Use = 'office'    WHERE ID % 6 = 1
UPDATE Buildings SET Use = 'market'    WHERE ID % 6 = 2
UPDATE Buildings SET Use = 'retail'    WHERE ID % 6 = 3
UPDATE Buildings SET Use = 'workshop'  WHERE ID % 6 = 4
UPDATE Buildings SET Use = NULL        WHERE ID % 6 = 5

This is almost certainly going to be easier and faster than a "random" approach. Then again, it might not be random enough.

这几乎肯定比“随机”方法更容易,更快捷。然后,它可能不够随意。

#1