update多语句更新(200分)

E

esxpd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #1

有access表a,b,c
我想用表a和b的动态汇总值,更新表c的字段值,主键是bh
update c set sl= Msl,je=Mje

注: Msl,Mje是下面语句得到
...............................
select bh,sum(sl) as Msl,sum(je) as Mje
(
select bh,sl,je from a
unon all
select bh,sl,je from b
) group by bh

 

E

esxpd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #2

请问这个update语句该怎么来写?

 

X

xinjinren

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #3

update c set sl= Msl,je=Mje from (你的查询语句)as a where c.bh=a.bh

 

勇

勇者

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #4

update c,
(select bh,sum(sl) as Msl,sum(je) as Mje
(
select bh,sl,je from a
unon all
select bh,sl,je from b
) group by bh)f
set c.sl= f.Msl,c.je=f.Mje
where c.bh=f.bh

 

H

hongxing_dl

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #5

update c set
sl= (select sum(sl)
from (select sl from a where bh=c.bh
unon all
select sl from b where bh=c.bh
)
),
je=(select sum(je)
from (select je from a where bh=c.bh
unon all
select je from b where bh=c.bh
)
)

 

H

hongxing_dl

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #6

估计这样也是支持的:
update c set
sl= ab.Msl,je=ab.Mje
from (select bh,sum(sl) as Msl,sum(je) as Mje
from (select bh,sl,je from a
unon all
select bh,sl,je from b
) group by bh
) ab
where c.bh=ab.bh

 

D

dcs_dcs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #7

update c,
(select bh,sum(sl) as Msl,sum(je) as Mje
(
select bh,sl,je from a
unon all
select bh,sl,je from b
) group by bh)f
set c.sl= f.Msl,c.je=f.Mje
where c.bh=f.bh

 

S

sefeng1982

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #8

update c set s1=a.s1+b.s1,je=a.je+b.je
from c
left join a on c.bh=a.bh
left join b on c.bh=b.bh

 

Z

zhaokaien

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #9

顶一下

 

A

aizhuzhu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #10

update c,
(select bh,sum(sl) as Msl,sum(je) as Mje (select bh,sl,je from a
unon all
select bh,sl,je from b ) group by bh) f
set c.sl= f.Msl,c.je=f.Mje
where c.bh=f.bh
7楼正确

 

E

esxpd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #11

update c,
(select bh,sum(sl) as Msl,sum(je) as Mje from
(select bh,sl,je from a
union all
select bh,sl,je from b )
group by bh) f
set c.sl= f.Msl,c.je=f.Mje
where c.bh=f.bh


错误提示:操作必须使用一个可更新的查询!??????

 

A

aizhuzhu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #12

update c set c.sl= f.Msl,c.je=f.Mje from
(select bh,sum(sl) as Msl,sum(je) as Mje
(
select bh,sl,je from a
unon all
select bh,sl,je from b
) group by bh)f

where c.bh=f.bh

 

E

esxpd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #13

to aizhuzhu:access好像不支持update中有,.....from语句.

 

A

aizhuzhu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #14

啊？不好意思看错了，以为是SQL，再看下

 

X

xinjinren

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #15

实在不行就再程序里面修改记录好了

 

勇

勇者

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #16

笨方法，建一个F表了，数据就是
select bh,sum(sl) as Msl,sum(je) as Mje from
(select bh,sl,je from a
union all
select bh,sl,je from b )
group by bh
然后UPDATE 更新了，

 

E

esxpd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #17

update c,( select bh,sl from a) as m2 set c.sl=m2.sl where c.bh=m2.bh
测试通过.
update c,( select bh,sl from a union all select bh,sl from a) as m2 set c.sl=m2.sl where c.bh=m2.bh
测试通不过,警告提示:操作必须使用一个可更新的查询!
可能access表用update更新数据时,select语句不能嵌套.

 

H

hongxing_dl

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #18

楼主试过这个也不行么？这个应该是可行的，只是可能效率有点低而已

update c set
sl= (select sum(sl)
from (select sl from a where bh=c.bh
unon all
select sl from b where bh=c.bh
)
),
je=(select sum(je)
from (select je from a where bh=c.bh
unon all
select je from b where bh=c.bh
)
)

 

E

esxpd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #19

to hongxing_dl:
也不行的.同样是操作必须使用一个可更新的查询!

 

E

esxpd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #20

这个问题好像不能解决.就这样了.谢谢大家.