使用来自其他表的随机值更新 SQL 表 [英] Update SQL table with random value from other table

问题描述

在 Microsoft SQL Server 2008 上，我有一个包含产品的表:

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

ID |姓名 |DefaultImageId

还有一张图片:

ID |产品编号 |字节

我想运行一个 Update 语句，该语句更新 Products 表中所有记录上的 DefaultImageId，并使用 Image 表中通过 ProductId 列与 Product 相关的随机 Id.

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.

有人可以帮忙吗?对于任何 SQL Champ 来说都应该很简单(显然不是我).

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

推荐答案

您可以对 NEWID 执行 order by 以获取更新的每一行的随机数.

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

这已被标记并添加了注释，表明它没有解决问题.我认为混乱来自人们没有意识到原始问题要求为每个产品选择随机图像，因此带有产品 ID 的 where 子句.提供了包含以下数据集的完整脚本.它为每个产品添加了五个产品和三个图像.然后为每个产品随机设置默认图片id.

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

这篇关于使用来自其他表的随机值更新 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！