update t2 set 数值=(select sum(数值)数值 from t1 where 类型1=t2.类型)from 表2 t2
update b set b.数值=a.数值 from (select 类型1,sum(数值) as 数值 from 表1 group by 类型1) a, b where a.类型1=b.类型
update b set b.数值=a.数值 from (select 类型1,sum(数值) as 数值 from 表1 group by 类型1) a, 表2 b where a.类型1=b.类型
update t2 set 数值=t1.数值 from 表2 t2,(select 类型1 ,sum(数值)数值 from 表1 group by 类型1)t1 where t2.类型=t1.类型1
--> 生成测试数据表:t1If not object_id('[t1]') is null Drop table [t1] Go Create table [t1]([类型1] nvarchar(1),[类型2] int,[数值] int) Insert t1 Select 'a',011,12 union all Select 'a',022,23 union all Select 'b',078,45 Go --Select * from t1--> 生成测试数据表:t2If not object_id('[t2]') is null Drop table [t2] Go Create table [t2]([类型] nvarchar(1),[数值] int) Insert t2 Select 'a',0 union all Select 'b',0 Go --Select * from t2-->SQL查询如下: update t2 set 数值=(select sum([数值]) from t1 where [类型1]=t2.[类型])select * from t2 /* 类型 数值 ---- ----------- a 35 b 45(2 行受影响) */
declare @a table(id varchar(12),num int) declare @b table(id varchar(12),num int)insert @a select 'a' , 12 union all select 'a' , 23 union all select 'b' , 45 insert @b select 'a', 0 union all select 'b', 0 update @b set num=a.num from (select id as id1,sum(num)as num from @a group by id) a where id=a.id1 id num ------------ ----------- a 35 b 45(所影响的行数为 2 行)
b
set
b.数值=a.数值
from
(select 类型1,sum(数值) as 数值 from 表1 group by 类型1) a,
b
where
a.类型1=b.类型
update
b
set
b.数值=a.数值
from
(select 类型1,sum(数值) as 数值 from 表1 group by 类型1) a,
表2 b
where
a.类型1=b.类型
from 表2 t2,(select 类型1 ,sum(数值)数值 from 表1 group by 类型1)t1
where t2.类型=t1.类型1
Drop table [t1]
Go
Create table [t1]([类型1] nvarchar(1),[类型2] int,[数值] int)
Insert t1
Select 'a',011,12 union all
Select 'a',022,23 union all
Select 'b',078,45
Go
--Select * from t1--> 生成测试数据表:t2If not object_id('[t2]') is null
Drop table [t2]
Go
Create table [t2]([类型] nvarchar(1),[数值] int)
Insert t2
Select 'a',0 union all
Select 'b',0
Go
--Select * from t2-->SQL查询如下:
update t2 set 数值=(select sum([数值]) from t1 where [类型1]=t2.[类型])select * from t2
/*
类型 数值
---- -----------
a 35
b 45(2 行受影响)
*/
declare @a table(id varchar(12),num int)
declare @b table(id varchar(12),num int)insert @a select
'a' , 12 union all select
'a' , 23 union all select
'b' , 45 insert @b select
'a', 0 union all select
'b', 0 update @b
set num=a.num
from (select id as id1,sum(num)as num from @a group by id) a
where id=a.id1
id num
------------ -----------
a 35
b 45(所影响的行数为 2 行)