在T-SQL中将列设置为计数

您可以使用相关的子查询。一种方法是:

update u
    set [count] = (select count(col1) from [User] u2 where u2.id = u.id)
    from [User] u;

但我可能会使用可更新的CTE:

with toupdate as (
      select u.*, count(u.col1) over (partition by u.id) as new_count
      from [User] u
     )
update toupdate
    set [count] = new_count;

注: count 和 user 是标识符的糟糕名称,因为它们与SQL关键字冲突。

update [User] u1
set [count] = (select count(*) 
               from [User] u2 
               where u1.col1 = u2.col1)

我通常会通过创建 FROM 子句计算所需的数据,然后将其重新联接到原始表中。

UPDATE [user]
SET [count] = repidck
FROM
    [user]
    INNER JOIN 
    (
        SELECT col1, COUNT(*) repidck
        FROM [user]
        GROUP BY col1
    ) counts
        ON counts.col1 = [user].col1

希望这有帮助