
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????

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.

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]