Update parent table with the sum of child table

gbrown · November 5th, 2004, 06:40 AM

Hi all

COuld anyone help with this one because its driving me nuts!!

I have a parent table that contains an order number and a total order value

The child table to this has order number, part code, qty and cost each.

What I am trying to do is write an SQL Update that sums all the child records and puts the value into the parent record so something like

update order_header set total_order_value=(select sum(item_qty*cost_each) from child where parent.order_number=child_order_number)

but this gives a syntax error in SQL Server.

Any help would be gratefully received.

Regards
Graham

happygv · November 9th, 2004, 07:35 AM

Hi Graham,

As per your description, I see only two tables involved in this. But where does the ORDER_HEADER table come from which you are trying to update. Can you explain that bit clearer? What is the error that it gives? can you post that too?

_________________________
- Vijay G
Strive for Perfection

happygv · November 9th, 2004, 07:53 AM

Hi Graham,

I suggest you use a cursor to do this as you cannot do that with just one update statement.

Code:

select order_number, sum(item_qty*cost_each) from child group by order_number order by order_number

This would result in what you are looking for. May be use can use INTO clause there in the above select statement to store it in to a separate table. Else go for a cursor based solution. That should be the right one.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection