SQL Server将随机数据从一个表复制到另一个表 [英] SQL Server Copy Random data from one table to another

问题描述

我有2个表东西和废话。 废话与东西的大小不同；在这种情况下，它的行数较少，但可能会更多。

I have 2 tables stuff and nonsense. nonsense is not the same size as stuff; in this case it is has fewer rows, but it may have more.

结构如下：

CREATE TABLE stuff (
    id INT PRIMARY KEY,
    details VARCHAR(MAX),
    data VARCHAR(MAX)
);

CREATE TABLE nonsense (
    id INT PRIMARY KEY,
    data VARCHAR(MAX)
);

东西表已经填充了详细信息，但数据现在为 NULL 。

The stuff table is already populated with details, but data is NULL for now.

我想随机从废话 的一个行复制数据放入填充的每一行。由于废话较小，自然会出现重复，这是可以的。

I would like to copy data from one row of nonsense at random into each row of stuff. Since nonsense is smaller, there will naturally be duplicates, which is OK.

这不会 工作：

UPDATE stuff
SET data=(SELECT TOP 1 data FROM nonsense ORDER BY NewId());

假定子查询在查询其余部分之前进行一次评估。但这是我想要的结果。

Presumably the sub query is evaluated once before the rest of the query. However that’s the sort of result I would have liked.

如何实现？

推荐答案

您需要关联子查询才能为每个记录运行它。

You need to correlate the sub-query to run it for each record.

UPDATE stuff
SET data=(SELECT TOP 1 data 
          FROM nonsense 
          where stuff.id = stuff.id  --here
          ORDER BY NewId());

这篇关于SQL Server将随机数据从一个表复制到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！