[SOLVED] SQL Server Copy Random data from one table to another

SQL Server Copy Random data from one table to another

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.

The structures are something like this:

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

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

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.

This does not work:

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.

How do I achive this?

Solution

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());