时间戳

首页 > 解决方案 > 使用 NEWID() 函数更新表

sql - 使用 NEWID() 函数更新表

问题描述

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

标签： sqlsql-servertsql

解决方案

像这样的东西

此查询随机选择 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];

推荐阅读