Professional Web Applications Themes

JOIN Update with SUM - MySQL

Trying to update a balance field to reflect the sum of a total of payments (of 3 types: payment, discount and adjustments). I am able to run a SELECT to view the info but can't get the update to work. Get error #1111 - Invalid use of group function . //WORKS SELECT invoice_summary.invoice_number,(invoice_summary.am ount_due - SUM(payments.payment_amount) - SUM(payments.discount_amount) - SUM(payments.adjustment_amount)) FROM invoice_summary,payments WHERE invoice_summary.invoice_number = payments.invoice_number GROUP BY invoice_summary.invoice_number //DOES NOT WORK UPDATE invoice_summary T1, payments T2 SET T1.balance = T1.amount_due - SUM(T2.payment_amount) - SUM(T2.discount_amount) - SUM(T2.adjustment_amount) where T1.invoice_number = T2.invoice_number *Error code = #1111 - Invalid use of group ...

  1. #1

    Default JOIN Update with SUM

    Trying to update a balance field to reflect the sum of a total of
    payments (of 3 types: payment, discount and adjustments). I am able to
    run a SELECT to view the info but can't get the update to work. Get
    error #1111 - Invalid use of group function .

    //WORKS
    SELECT invoice_summary.invoice_number,(invoice_summary.am ount_due -
    SUM(payments.payment_amount) - SUM(payments.discount_amount) -
    SUM(payments.adjustment_amount)) FROM invoice_summary,payments WHERE
    invoice_summary.invoice_number = payments.invoice_number GROUP BY
    invoice_summary.invoice_number

    //DOES NOT WORK
    UPDATE invoice_summary T1, payments T2 SET T1.balance = T1.amount_due -
    SUM(T2.payment_amount) - SUM(T2.discount_amount) -
    SUM(T2.adjustment_amount) where T1.invoice_number = T2.invoice_number
    *Error code = #1111 - Invalid use of group function

    //WORKS

    UPDATE invoice_summary T1, payments T2 SET T1.balance = T1.amount_due -
    T2.payment_amount - T2.discount_amount - T2.adjustment_amount where
    T1.invoice_number = T2.invoice_number

    WHAT AM I MISSING????

    fmdevelopertim@gmail.com Guest

  2. #2

    Default Re: JOIN Update with SUM


    com wrote: 

    I would guess that you need to recreate your query using subselects and
    GROUP BY. But I am only guessing.

    Daz Guest

  3. #3

    Default Re: JOIN Update with SUM

    Adding an order by makes no difference. Tried a subquery:

    UPDATE invoice_summary T1, payments T2 SET T1.balance = (SELECT
    (invoice_summary.amount_due - SUM(payments.payment_amount) -
    SUM(payments.discount_amount) - SUM(payments.adjustment_amount)) FROM
    invoice_summary,payments WHERE invoice_summary.invoice_number =
    payments.invoice_number GROUP BY invoice_summary.invoice_number)

    but get error: 1093 - You can't specify target table 'invoice_summary'
    for update in FROM clause.

    Anyone have a suggestion aside from a temp join table?
    Daz wrote: 
    >
    > I would guess that you need to recreate your query using subselects and
    > GROUP BY. But I am only guessing.[/ref]

    fmdevelopertim@gmail.com Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. MySQL Update/ Join Questions
    By Cyber_will in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 3rd, 02:09 PM
  3. trying to update a table after making a join select query on two tables
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: March 1st, 10:33 PM
  4. Replies: 2
    Last Post: September 18th, 09:59 PM
  5. UPDATE with inner join
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 4th, 03:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139