Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Unanswered: SQL for update balance from two tables

    hello ,

    i need help in updating a balance column in table. using update command

    The balance column in present in customer_master

    customer_master > custcode,balance

    It needs to be updated on the basis of values in two tables

    Bookings > custcode, bookingamount
    Receivables > custcode, amountrecd

    for a customercode, the Balance is bookingamount - amountrecd

    The command is to be applied in mysql and postgresql.

    It can be for a single customer like (for custcode ='A1234XXXXX') and also may be applied for all customers present in customer_master.


  2. #2
    Join Date
    Aug 2011
    Ok, i got the answer, so i may just update it as well

    UPDATE customer_master a
    LEFT JOIN (select custcode, sum(COALESCE(bookingamount, 0)) AS bookingamount
    from bookings group by custcode) b
    ON a.custcode = b.custcode
    LEFT JOIN (select custcode, sum(COALESCE(amountrecd, 0)) AS amountrecd
    from receivables group by custcode) c
    ON a.custcode = c.custcode
    a.balance = COALESCE(b.bookingamount, 0) - COALESCE(c.amountrecd, 0)
    -- WHERE a.custcode = 'A1234XXXXX'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts