May 21, 2003 at 6:44 am
this ought to be really easy but I can't spot it.
I want to randomly update an existing column of a table from a column in another table.
I thought that this should work:
update tbl1 set fldToRandomize = (select top 1 randomValue from randomValues order by NEWID())
but that seems to only execute the select once - I get the *same* random value in all rows, rather than a different one.
Any obvious insights?
tia
Tim
May 27, 2003 at 8:00 am
This was removed by the editor as SPAM
May 27, 2003 at 8:16 am
try this
DECLARE @RandomValues TABLE
(
RandValint
)
DECLARE @Tbl TABLE
(
Idint identity,
RandValint
)
INSERT INTO @RandomValues
VALUES (1)
INSERT INTO @RandomValues
VALUES (4)
INSERT INTO @RandomValues
VALUES (6)
INSERT INTO @RandomValues
VALUES (10)
INSERT INTO @Tbl (RandVal)
VALUES (NULL)
INSERT INTO @Tbl (RandVal)
VALUES (NULL)
INSERT INTO @Tbl (RandVal)
VALUES (NULL)
INSERT INTO @Tbl (RandVal)
VALUES (NULL)
UPDATE @Tbl
SET RandVal =
(
SELECT TOP 1 T1.RandVal
FROM @RandomValues As T1
CROSS JOIN @Tbl As T2
WHERE T2.ID = T3.Id
ORDER BY NEWID()
)
FROM @Tbl As T3
SELECT * FROM @Tbl
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply