Update SQL table with random value from other table
Addressing @philreed's issue with the selected answer:
Is there a way to assign each row being updated with a different value randomly chosen from the source table?
UPDATE Products
SET DefaultImageId = t2.Id
FROM Products t1
CROSS APPLY (
SELECT TOP 1 Id
FROM Images
WHERE t1.Id = t1.Id
ORDER BY newid()
) t2
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()
)
This has been down marked and comments added indicating it does not solve the problem. I think the confusion has come from where people have not realised the original question requests a random image be selected for each product, hence the where clause with Product Id. Have provided a full script with data set below. It adds five products and three images for each product. Then randomly sets the default image id for each product.
CREATE TABLE Products(Id INT, Name NVARCHAR(100), DefaultImageId INT NULL)
CREATE TABLE Images (Id INT, ProductId INT, Bytes VARBINARY(100))
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(1, 'A', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(2, 'B', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(3, 'C', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(4, 'D', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(5, 'E', NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(1, 1, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(2, 1, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(3, 1, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(4, 2, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(5, 2, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(6, 2, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(7, 3, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(8, 3, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(9, 3, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(10, 4, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(11, 4, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(12, 4, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(13, 5, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(14, 5, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(15, 5, NULL)
UPDATE
Products
SET
DefaultImageId =
(
SELECT TOP 1
Id
FROM
Images
WHERE
Images.ProductId = Products.Id
ORDER BY
NEWID()
)
SELECT * FROM Products