更新问题

[kuangdp]

declare @tb_1 table
(
type int,
num int
)
insert into @tb_1
select 1,10
union
select 2,15
union
select 3,25


declare @tb_2 table
(
ID int ,
f0010 int,
f0020 int,
f0030 int
)

insert into @tb_2 (ID)
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7


如何用一条语句更新@tb_2得到：
1 10 15 25
2 10 15 25
3 10 15 25
4 10 15 25
5 10 15 25
6 10 15 25
7 10 15 25

[iamme81]

[Quote=引用 6 楼 kuangdp 的回复:]
其实我是想找到类似我下面的错误写法的更新语句；

如果用你们的是可以，但是写的会很长，谁能用好的写法来实现我想得到的结果

SQL codedeclare@tb_1table(
typeint,
numint)insertinto@tb_1select1,10unionselect2,15unionselect3,25unionselect4,30declare@tb_2table(
IDint,
f0010int,
f0020int,
f0030int)insertinto@tb_2(ID)select1unionselect2unionselect3unionselect4unionselect5unionsel…
[/Quote]
只用后面半段就可以了,似乎你这种写法已是比较简便的了，若要更为简单尝试动态SQL语句

[Limpire]

update @tb_2 set 
	f0010=(select num from @tb_1 where type=1),
	f0020=(select num from @tb_1 where type=2),
	f0030=(select num from @tb_1 where type=3)

[sweetweiwei]

update @tb_2
set f0010 = 10,
f0020 = 15,
f0030 = 25

[昵称被占用了]

declare @tb_1 table 
( 
type int, 
num int 
) 
insert into @tb_1 
select 1,10 
union 
select 2,15 
union 
select 3,25 


declare @tb_2 table 
( 
ID int  , 
f0010 int, 
f0020 int, 
f0030 int 
) 

insert into @tb_2 (ID) 
select 1 
union 
select 2 
union 
select 3 
union 
select 4 
union 
select 5 
union 
select 6 
union 
select 7 

update @tb_2 set 
f0010=(select num from @tb_1 where type=1),
f0020=(select num from @tb_1 where type=2),
f0030=(select num from @tb_1 where type=3)

select * from @tb_2

[撩天槌]

update @tb_2 set f0010=a.num,f0020=b.num,f0030=c.num
from (select num from @tb_1 where type=1)a,
(select num from @tb_1 where type=2)b,
(select num from @tb_1 where type=3)c

[SlinkLee]

如果有两条或者更多地记录对应type = 1？

[kuangdp]

其实我是想找到类似我下面的错误写法的更新语句；

如果用你们的是可以，但是写的会很长，谁能用好的写法来实现我想得到的结果

declare @tb_1 table 
(
	type int,
	num int
)
insert into @tb_1
select 1,10
union
select 2,15
union
select 3,25
union 
select 4,30


declare @tb_2 table 
(
	ID int  ,
	f0010 int,
	f0020 int,
	f0030 int
)

insert into @tb_2 (ID)
select 1
union 
select 2
union 
select 3
union 
select 4
union 
select 5
union 
select 6
union 
select 7
 
select * from @tb_2

select case when type=1 then 1 
			  when type in(2,3) then 2 
			  when type=4 then 3 end as type,sum(num) as sum_num
		from @tb_1 
		group by case when type=1 then 1 
			  when type in(2,3) then 2 
			  when type=4 then 3 end 


update a set a.f0010=case when b.type=1 then b.sum_num end,
  		a.f0020=case when b.type=2 then b.sum_num end,
  		a.f0030=case when b.type=3 then b.sum_num end 
from @tb_2 as a,(select case when type=1 then 1 
			  when type in(2,3) then 2 
			  when type=4 then 3 end as type,sum(num) as sum_num
		from @tb_1 
		group by case when type=1 then 1 
			  when type in(2,3) then 2 
			  when type=4 then 3 end 
		) as b

[jhwcd]

[Quote=引用 4 楼 th199 的回复:]
SQL codeupdate @tb_2 set f0010=a.num,f0020=b.num,f0030=c.num
from (select num from @tb_1 where type=1)a,
(select num from @tb_1 where type=2)b,
(select num from @tb_1 where type=3)c
[/Quote]

[SlinkLee]

Expecting......