sql - 如何用随机值更新列中的所有行？

Question

假设一个名为 data_records 的表有 5 条记录 和 2 列(C1 和 C2)。

目标是用不同的随机值更新 C2 列的所有行。

以下不起作用:

UPDATE data_records SET
  C2 = d.r
FROM
  (SELECT random() as r, generate_series(1, 5) as g) as d

因为它用相同的数字更新 C2 的所有行。

子查询

SELECT random() as r, generate_series(1, 5) as g

但是，当单独执行时会生成我想要的表格，即每行一个随机值。

如何获得我描述的行为？

更新

我尝试使用子查询进行更新的原因是我的目标列的类型为 jsonb。我的 SET 子句看起来更像这样:

SET C2 = jsonb_set(C2, '{variation}', to_jsonb(d.r))

感谢评论，我意识到我最初的问题缺少一个基本原理。

谢谢。

Answer 1

demo: db<>fiddle

您的 JSON 的简单格式是:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(random()));

---

如果您想要带有 generate_series 的第二列(无论如何)，您将需要一些东西来加入原始表。 generate_series 可以为您提供从 1 到 5 的行。因此，要加入 data_records，您还需要 1 到 5 列。如果这是保存在 c1 中的内容，则没有问题。只需加入 c1。

但如果不是，则必须生成它，可能使用 row_number 窗口函数将行计数添加为列。然后，您可以将行计数加入到 generated_series 列中，并且对于每个 c1 和 ，您都有一个包含 random 值的行c2。其中之一应该是独一无二的。这个唯一的列(在我的例子中是 c1)用作 UPDATE 子句的 WHERE 过滤器。当然这可能是 c2。但是，如果它们不是唯一的，那么对于相同的 c1/c2 值，您将以相同的 random 值结束:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(rand.r))
FROM 
    (SELECT *, row_number() OVER () rn FROM data_records) dr_rn
LEFT JOIN
    (SELECT generate_series(1, 5) gs , random() r) rand 
ON dr_rn.rn = rand.gs

WHERE dr.c1 = dr_rn.c1;

如果你有一个唯一的 id 列，那真的会更简单。但尽管如此，我看不出有任何理由使它变得如此复杂。