表A
id Bcount
1 null
2 null
3 null
表B
id Aid
1 2
2 1
3 1Aid为表A的id,
现在要更新表A的Bcount字段,结果如下:
表A
id Bcount
1 2
2 1
3 null
id Bcount
1 null
2 null
3 null
表B
id Aid
1 2
2 1
3 1Aid为表A的id,
现在要更新表A的Bcount字段,结果如下:
表A
id Bcount
1 2
2 1
3 null
UPDATE A set Bcount = B.id
FROM A a inner join B b on a.ID= b.AID
update a set bcount=(select count(1) from b where a.id=b.aid group by b.aid)
create table b (id int identity,aid int)
insert a select null union all select null union all select null
insert b select 2 union all select 1 union all select 1
select * from a
/*
id bcount
1 NULL
2 NULL
3 NULL
*/
select * from b
/*
id aid
1 2
2 1
3 1
*/
update a set bcount=(select count(1) from b where a.id=b.aid group by b.aid)/*
id bcount
1 2
2 1
3 NULL
*/drop table a
drop table b