Update table field with sum of other table field

Posted on 2004-11-04
Last Modified: 2009-12-16
I am using SQL Server 2000.

I have a table called tblPartners and I want to run an sp every night that updates a field called TotalPaid.

The table that holds payment info is called tblPaymentInstalments and the field is called InstalmentAmnt.

The 2 tables are related via 2 other tables as follows:

tblPaymentInstalments inner join
tblPayments on tblPaymentInstalments.PaymentRef = tblPayments.PaymentRef inner join
tblProposals on tblPayments.ProposalNo = tblProposals.ProposalNo inner join
tblPartners on tblProposals.PartnerID = tblPartners.PartnerID

How do I write the sp that will update tblPartners.TotalPaid with the sum(tblPaymentInstalments.InstalmentAmnt) for each Partner?

Question by:naqayya
    LVL 50

    Accepted Solution

      SET TOTALPAID = u.amt
     from tblpartners as p
     inner Join (select pro.partnerID,sum(pi.InstallmentAmnt) as amt
                       from  tblPaymentInstalments as pi
                      inner join tblPayments as P
                         on PI.PaymentRef = P.PaymentRef
                      inner join tblProposals as Pro
                         on P.ProposalNo = Pro.ProposalNo
                       group by pro.partnerID
                       having sum(pi.InstallmentAmnt) <> 0
         on U.PartnerID = P.PartnerID


    Author Comment

    Thanks Lowfatspread, that worked beautifully!

    (you forgot the 'as u' after subquery bracket, but no probs)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now