我在项目中使用 SQL Server 2014。我有两个表(主和细节关系(,如下所示:
Master:
id
detail_count (the number of associated Detail records)
Detail:
id
master_id
列detail_count
是主记录具有的明细记录数。我能够找到每个主记录的计数,如下所示:
select m.id, count(*) from Master m
join Detail d on d.master_id = m.id
group by m.id
我还能够为主表编写更新语句:
update Master set detail_count = ?
如何结合上述两个语句来更新所有主记录的detail_count
您可以使用以下查询简单地更新计数:
update Master
set detail_count = (select count(*) from detail where detail.master_id = Master.id)