关于SQL面试题，帮忙解决

[hbxfgj]

,现有表bill和表payment结构如下，
两表通过pay_id关联：    
bill表――bill_id是帐单标识，charge为帐单金额，pay_id为付款编号，PK：bill_id
    bill_id   INTEGER    
charge   INTEGER    
pay_id   INTEGER      
 payment表――pay_id为付款编号，charge为付款金额，PK：pay_id    
pay_id   INTEGER    
charge   INTEGER        
要求更新payment使charge=bill表中同一pay_id的charge之和。  

[fcuandy]

UPDATE a SET charge = sCharge
    FROM payment a
INNER JOIN 
    (SELECT SUM(charge) sCharge,pay_id FROM bill GROUP BY payer_id) b
    ON a.pay_id=b.pay_id

[tim_spac]

update a
set charge = b.sumcharge
from payment a
join (select pay_id, sumcharge=sum(charge) 
      from bill 
      group by pay_id) as b on a.pay_id=b.pay_id

[hbxfgj]

上边的我试了不可以，有语法上的错误
update payment a , (select sum(charge) sCharge,pay_id from bill GROUP by pay_id) b
set a.charge=b.sCharge
where a.pay_id=b.pay_id

[pt1314917]

               

update payment set charge=(select sum(charge) from bill where pay_id=a.pay_id) from payment a

[nj_1st_excellence]

/*
create table bill
(
bill_id int,
charge int,
pay_id int
)
create table payment
(
pay_id int,
charge int
)
insert bill values(1,200,1)
insert bill values(2,200,1)
insert bill values(3,200,1)
insert bill values(4,200,1)
insert bill values(5,200,1)
insert bill values(6,200,1)
insert bill values(1,200,2)
insert bill values(2,200,2)
insert bill values(3,200,2)
insert bill values(4,200,2)
insert bill values(5,200,2)
insert bill values(6,200,2)

insert payment(pay_id) values(1)
insert payment(pay_id) values(2)*/

update payment set charge = bb.charge from payment aa,
(select b.pay_id,sum(isnull(a.charge,0)) charge from bill a left join payment b on a.pay_id=b.pay_id group by b.pay_id) bb
where aa.pay_id=bb.pay_id