关于多列update的问题谢谢大家

[bobozc]

update b
set a = (select count(列1) from 表2 ),b = (select sum(列2) from 表2 ),
from 表1 b


这个写法比只更新一列要慢一倍,有什么简化方法吗

下面这种方法好象不行说错误

update b
set (a,b) = (select count(列1),sum(列2) from 表2)
from 表1 b


谢谢大家

[jinghuanzhang]

路过中学习。。。

[htl258_Tony]

[Quote=引用 13 楼 bobozc 的回复:]
引用 7 楼 htl258 的回复:
SQL codeupdate bset a= t.a,b= t.bfrom 表1 b ,
  (selectcount(列1) a,sum(列2) bfrom 表2) t


会提示:无法绑定由多个部分组成的标识符 b.aaa

update b
set a = t.a,b = t.b
from 表1 b ,
  (select count(列1) a,sum(列2) b  from 表2 where b.aaa = 1) t

麻烦大家了
[/Quote]

update b 
set a = t.a,b = t.b 
from 表1 b , 
  (select count(列1) a,sum(列2) b  from 表2 where aaa = 1) t 

把B给去掉.

[htl258_Tony]

update b 
set a = t.a,b = t.b 
from 表1 b , 
  (select count(列1) a,sum(列2) b  from 表2) t 
where b.aaa=1

如果你的aaa在表1,这样.

[tengjian1981]

update b
set a = t.a,b = t.b
from 表1 b ,
(select count(列1) a,sum(列2) b from 表2 where aaa = 1) t

[spsx99vj]

update b set b.a=a.a,b.b=a.b
from 表1 b,(select count(列1) a,max(列2) b from 表2) a

[bobozc]

update b
set a = t.a,b = t.b
from 表1 b ,
(select count(列1) a,sum(列2) b from 表2 where b.列3 = 列3) t

还是没解决哦,,,我条件里一定会有一个 B 的

提示:无法绑定由多个部分组成的标识符 b.列3

[feixianxxx]

[Quote=引用 20 楼 bobozc 的回复:]
update b
set a = t.a,b = t.b
from 表1 b ,
  (select count(列1) a,sum(列2) b  from 表2 where b.列3 = 列3) t

还是没解决哦,,,我条件里一定会有一个  B 的

提示:无法绑定由多个部分组成的标识符 b.列3
[/Quote]
你有看14楼的代码么？

[fanzhouqi]

[Quote=引用 20 楼 bobozc 的回复:]
update b
set a = t.a,b = t.b
from 表1 b ,
  (select count(列1) a,sum(列2) b  from 表2 where b.列3 = 列3) t

还是没解决哦,,,我条件里一定会有一个  B 的

提示:无法绑定由多个部分组成的标识符 b.列3
[/Quote]红字的换一下

[guguda2008]

--用變量
DECLARE @I INT
select @INT=count(列1) from 表2 

update b
set a =@I,b =  @I 
from 表1 b 

[liangCK]

UPDATE A SET
    a = B.rowcnt,
    b = B.rowsum
FROM tb1 AS A,
  (SELECT COUNT(col1) AS rowcnt,SUM(col2) AS rowsum FROM tb) AS B;

[guguda2008]

你這樣寫每行都會算一遍的

[SQL77]

DECLARE @I INT
select @INT=count(列1) from 表2 

DECLARE @B INT
select @B=SUM(列2) from 表2 

update b
set a =@I,b =  @B
from 表1 b 

[SQL77]

DECLARE @I INT
select @I=count(列1) from 表2 

DECLARE @B INT
select @B=SUM(列2) from 表2 

update b
set a =@I,b =  @B
from 表1 b 

宝鸭名字都不打对,呼呼

[guguda2008]

[Quote=引用 5 楼 sql77 的回复:]
SQL codeDECLARE@IINTselect@I=count(列1)from 表2DECLARE@BINTselect@B=SUM(列2)from 表2update bset a=@I,b=@Bfrom 表1 b
宝鸭名字都不打对,呼呼
[/Quote]
看錯了，呵呵，梁哥的那個是正解

[htl258_Tony]

update b 
set a = t.a,b = t.b
from 表1 b ,
  (select count(列1) a,sum(列2) b  from 表2) t

[--小F--]

update
a
set
a = B.rowcnt,
b = B.rowsum
from
tb1 a,(select count(col1) rowcnt,sum(col2) rowsum from tb) B;

[--小F--]

update 
    a 
set 
    a = B.rowcnt, 
    b = B.rowsum 
from 
    tb1 a,(select count(col1)  rowcnt,sum(col2)  rowsum from tb) B;

[feixianxxx]

update b 
set a =  count(列1) ,
b =   sum(列2) 
from 表1 b 
,表2

[水族杰纶]

--try


update b 
set a = (select rows from sysindexes where id=object_id('表2') and indid=0 ),b =  (select sum(列2) from 表2 )
from 表1  b

[xiequan2]

1:
;
with cte1 as
(

 select * from b,(select count(列1) rowcnt1,count(列2) rowcnt2 from 表2) B
)

update cte1 set a=rowcnt1,b=rowcnt2


2:t-sql到目前为止还不支持行值构造器，虽然Ansi支持