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

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

Hi Graham,

I suggest you use a cursor to do this as you cannot do that with just one update statement.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