com wrote:
I would guess that you need to recreate your query using subselects and
GROUP BY. But I am only guessing.
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 ...
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????
com wrote:
I would guess that you need to recreate your query using subselects and
GROUP BY. But I am only guessing.
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]
Bookmarks