sql把一张表的数据更新到另一种表

Question

两张表 table1 table2;
table1 中，项 name v1 v2
qb 1 1
qb 2 2
qb 3 3
zw 1 1
zw 2 2
zw 3 3
zw 4 4

table2 name v1 v2
qb 0 0
zw 0 0

我要统计 qb，zw 完成 v1,v2的大于等于2次数，然后更新到table2中

结果就像是

table2 name v1 v2
qb 2 2
zw 3 3

这个怎么弄？
请高手指点
经过 3 6 楼 方案
都是现实“无法绑定由多个部分组成的标识符”怎么办？

Answer 1

update table2 set v1=(select COUNT(v1) from table1 where table1.name=table2.name and table1.v1>1),
v2=(select COUNT(v2) from table1 where table1.name=table2.name and table1.v2>1)

试一下这个本回答被提问者采纳

Answer 2

update table2 set v1=(select v1 from (select name,count(v1) v1,count(v2) v2 from table1 group by name having count(v1)>=2 and count(v2)>=2) b where a.name=b.name);

update table2 set v2=(select v2 from (select name,count(v1) v1,count(v2) v2 from table1 group by name having count(v1)>=2 and count(v2)>=2) b where a.name=b.name);

分两步写

Answer 3

declare @tb1 table ([name] varchar(10),v1 int,v2 int)
insert into @tb1 select 'qb',1,1
insert into @tb1 select 'qb',2,2
insert into @tb1 select 'qb',3,3
insert into @tb1 select 'zw',1,1
insert into @tb1 select 'zw',2,1
insert into @tb1 select 'zw',3,3
insert into @tb1 select 'zw',4,4

declare @tb2 table([name] varchar(10),v1 int,v2 int)
insert into @tb2 select 'qb',0,0
insert into @tb2 select 'zw',0,0

update cc
set cc.v1=c1,cc.v2=c2
from @tb2 cc join (
select [name],count(v1) as c1 from @tb1 a where v1>=2 group by [name]) aa on cc.[name]=aa.[name]
left join (select [name],count(v2) as c2 from @tb1 where v2>=2 group by [name]) bb on aa.[name]=bb.[name]

select * from @tb2

Answer 4

sql server :

update table2
set v1=b.v1,v2=b.v2
from table2 a left join
(select name,sum(v1) v1,sum(v2) v2 from table1 group by name having sum(v1)>=2 and sum(v2)>=2) b on a.name=b.name
where b.name is not null

Answer 5

SELECT *
INTO table2
FROM table1
WHERE xxx
这样试试看行不