Update SQL table with random value from other table

Question

Hi

On Microsoft SQL Server 2008, I have a table with Products:

Id | Name | DefaultImageId

And one with Images:

Id | ProductId | Bytes

I want to run an Update statement, that updates the DefaultImageId on all records in the Products table with a random Id from the Images table that is related to the Product via the ProductId column.

Can anyone help out? Should be simple for any SQL Champ (Which is obviously not me)..

Answer 1

Check this out.

Update Products
Set DefaultImageId = (
SELECT top 1 Id 
From Images 
Where 1=1
and Products.Id = Images.ProductId
ORDER BY NEWID()
)

Answer 2

You can do an order by on a NEWID to get a random number for every row of your update.

UPDATE
 Products
SET
 DefaultImageId =
 (
  SELECT TOP 1
   Id
  FROM
   Images
  WHERE
   Images.ProductId = Products.Id
  ORDER BY
   NEWID()
 )