使用NEWID()函数更新表

Question

CREATE TABLE Products(Id INT, Name CHAR(100), DefaultImageId INT NULL);

INSERT INTO Products (Id, Name, DefaultImageId) VALUES(1, 'A', NULL);
INSERT INTO Products (Id, Name, DefaultImageId) VALUES(2, 'A', NULL);
INSERT INTO Products (Id, Name, DefaultImageId) VALUES(3, 'A', NULL);
INSERT INTO Products (Id, Name, DefaultImageId) VALUES(4, 'A', NULL);
INSERT INTO Products (Id, Name, DefaultImageId) VALUES(5, 'A', NULL);
INSERT INTO Products (Id, Name, DefaultImageId) VALUES(1, 'B', NULL);
INSERT INTO Products (Id, Name, DefaultImageId) VALUES(2, 'B', NULL);
INSERT INTO Products (Id, Name, DefaultImageId) VALUES(3, 'B', NULL);

通常，我会像下面的脚本一样随机更新一个表。

update a
    set DefaultImageId=1
from Products as a
where name = 'A' 
and id in (
    select top 2 id
    from Products as b
    where a.name = b.name
    order by newid()
)

不过，我遇到了一些问题。它将更新多于/少于2行。为了调试，我尝试多次执行以下脚本。结果并不总是只有两个记录。如果我通过newid()删除订单，输出结果的数量将很好。这似乎是newid()中的问题。我该如何解决这个问题？谢谢

select * 
from Products as a
where name = 'A' 
and id in (
    select top 2 id
    from Products as b
    where a.name = b.name
    order by newid()
)

Answer 1

你可以试试这个：

UPDATE U SET DefaultImageId = 1
FROM(
      SELECT TOP 2 * FROM dbo.Products WHERE Name = 'A' ORDER BY NEWID()
) AS U

但是，如果您在内部查询中过滤掉DefaultImageId=1，那么它也会更好。您应该注意到，在这种情况下，内部查询可能生成少于2个记录。

UPDATE U SET DefaultImageId = 1
FROM(
      SELECT TOP 2 * FROM dbo.Products WHERE Name = 'A' AND DefaultImageId <> 1 ORDER BY NEWID()
) AS U

Answer 2

就像这样

此查询随机选择2行。

with top_2_cte(id, [name]) as (
    select top 2 id, [name] 
    from Products 
    where name='A' 
    order by newid())
select a.* 
from Products a
     join
     top_2_cte ttc on a.Id=ttc.id
                      and a.[Name]=ttc.[name];

更新语句

;with top_2_cte(id, [name]) as (
    select top 2 id, [name] 
    from Products 
    where name='A' 
    order by newid())
update a
    set DefaultImageId=1
from Products a
     join
     top_2_cte ttc on a.Id=ttc.id
                      and a.[Name]=ttc.[name];

Answer 3

我想你只是想找一个简单的JOIN

UPDATE A
SET DefaultImageId = 1
FROM Products A
JOIN 
  (
    SELECT TOP 2 Id, Name
    FROM Products B
    ORDER BY NEWID()
  ) B ON A.Id = B.Id AND A.Name = B.Name;

演示