Link to home
Start Free TrialLog in
Avatar of skij
skijFlag for Canada

asked on

MySQL: Update column with sum of other columns

What is wrong with this MySQL query? 
UPDATE `sales` SET `sale_received` =  sum(`sale_products_subtotal` + `sale_shipping_charged` + sale_tax) WHERE `sale_payment_status` = 'Paid in Full'

Open in new window

It returns an error:
Error: Invalid use of group function
Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)
Flag of United States of America image
It's hard to say exactly without knowing the schema of your table.  I suspect you only want to sum sale by sale that are paid in full and not a sum of all sales that are paid in full.  In order to keep the sum to the sale itself and not an overall sum, you'll need to group by the sale id.

UPDATE sales AS s
JOIN
 (SELECT sale_id,
   SUM(sale_products_subtotal + sale_shipping_charged + sale_tax) as sum_sale_received
    WHERE sale_payment_status = 'Paid in Full' 
    GROUP BY sale_id) AS grp
   ON grp.sale_id = s.sale_id
SET s.sale_received = grp.sum_sale_received

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of themrrobert
themrrobert
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skij
skij
Flag of Canada image

ASKER

Thank you both. zephyr_hex, your idea returned an error:
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE sale_payment_status = 'Paid in Full' GROUP BY sale_id) AS grp ON' at line 5

themrrobert, your idea worked.