Updating multiple row with random data from another table?

Question

Combining some examples, I came up with the following query (fields and table names have been anonymised soI hope I didn't insert typos).

UPDATE destinationTable
SET destinationField = t2.value
FROM destinationTable t1
CROSS APPLY (
    SELECT TOP 1 'SomeRequiredPrefix ' + sourceField as value
    FROM #sourceTable
    WHERE sourceField <> ''
    ORDER BY NEWID()
    ) t2

Problem Currently, all records get the same value into destinationField , value needs to be random and different. I'm probably missing something here.

SQLChao · Accepted Answer · 2017-10-05 15:19:43Z

2

Here's a possible solution. Using CTE's assign row numbers to both tables based on random order. Join the tables together using that rownumber and update the rows accordingly.

;WITH 
dt AS 
(SELECT *, ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNum
FROM dbo.destinationtable),
st AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNum
FROM dbo.#sourcetable)

UPDATE dt
SET dt.destinationfield = 'SomeRequiredPrefix ' + st.sourcefield
FROM dt
JOIN st ON dt.RowNum = st.RowNum

UPDATED SOLUTION

I used CROSS JOIN to get all possibilities since you have less rows in source table. Then assign random rownumbers and only take 1 row for each destination field.

;WITH cte
AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY destinationfield ORDER BY NEWID()) AS Rownum
    FROM destinationtable
    CROSS JOIN #sourcetable
    WHERE sourcefield <> ''

)

UPDATE cte
SET cte.destinationfield = 'SomeRequiredPrefix ' + sourcefield
WHERE cte.Rownum = 1

SELECT * FROM dbo.destinationtable

edited Oct 5, 2017 at 15:19

answered Oct 4, 2017 at 15:56

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TTT Over a year ago

This is working better but not yet doing everything it should. Data are randomise but #sourcetable as far less records the destination table, hence I'm thinking of using a LEFT OUTER JOIN, otherwise #sourceTable is limiting the number of row update in destinations table. Still it seems important to keep "WHERE sourceField <> ''" since a lot of source records have '' in sourceField.

TTT Over a year ago

Yet are you sure it's "UPDATE dt"? "Not UPDATE destinationtable"?

TTT Over a year ago

In ST: 3.000 in total, but really a lot where sourceField = [blank string]. In DT ~82.000. For now I went for a cursor instead of a single query.

SQLChao Over a year ago

@TTT I've added another possible soluition.

TTT Over a year ago

With that one I get (547 row(s) affected), where I should have (82067 row(s) affected). Also the execution time of a cursor is 00:19, the execution time of the updated solution is 00:55, so for now I'm staying with the cursor.

Collectives™ on Stack Overflow

Updating multiple row with random data from another table?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related