mysql - sql-按计数更新列

我在MySQL中编写了以下查询：

UPDATE mytable atable,
(SELECT address_one, address_two, COUNT(*) cnt FROM table
GROUP BY address_one, address_two) btable SET atable.address_count = btable.cnt
WHERE atable.address_one = btable.address_one AND atable.address_two = btable.address_two

它计算表中列出了多少address_one和address_two并将数字存储在相应的address_count中。

但是，这在MySQL中可以正常工作，而在SQL Server中则不能。如何为SQL Server修复此问题？

最佳答案

尝试这个：

update a
set a.address_count = b.cnt
from mytable a
join (
    select address_one,
        address_two,
        COUNT(*) cnt
    from mytable
    group by address_one,
        address_two
    ) b on a.address_one = b.address_one
    and a.address_two = b.address_two

旁注：始终使用显式和现代的联接语法，而不是基于旧逗号的联接。

关于mysql - sql-按计数更新列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42468349/