Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2005-09-07 : 23:47:10
|
| I have 2 tables 1. tbl_quote2. tbl_quote_chargestbl_quote contains fields called quote_id which is primary key here and total_quote_chargestbl_quote_charges contains fields called quote_id,number_of_payments,quote_amouuntsHere the total_quote_charges for a quote_id should be updated from tbl_quote_charges where its the sum of (number_of_payments*quote_amounts)select quote_id,sum(quote_amounts*(case when number_of_payments=0 then 1 else number_of_payments end))as amount from tbl_temp_reinstatement_quote_charges where group by quote_idThis gives the value of the total quote amount for each quote_id but how can i update it in the tbl_quote table |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2005-09-08 : 00:04:47
|
| tbl_quote_charges contains multiple quote_id and the corressponding values for which the calculation should be done and update in the other table |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-08 : 01:41:15
|
| Before trying this query take a back of those tablesIf the updation is wrong restore themUpdate t1 set t1.total_quote_charges=T2.amount from tbl_quote t1 inner join (select quote_id,sum(quote_amounts*(case when number_of_payments=0 then 1 else number_of_payments end))as amount from tbl_temp_reinstatement_quote_charges where group by quote_id) T2 on t1.quote_id=T2.quote_idMadhivananFailing to plan is Planning to fail |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2005-09-08 : 22:47:32
|
| This works ............Thanks a lot |
|
|
|
|
|
|
updation issue