--- In firebird-support@yahoogroups.com, "majstoru" <majstoru@y...> wrote:

>
> Hi,
>
> I have a small problem, maybe is not a problem, but an expret trick.
> I'm using Firebird 2 and I have two tables fex: Customers, Orders! I
> would like to write a SQL statment to update Customers (field
> TotalOrders) from table Orders.
> Something like:
> UPDATE Customers
> SET TotalOrders = SUM(Orders.TotalOrders)
> // begin of the problem
> The continue of this SQL must update all customers form table orders,
> something like FOR DO (in delphi).

Well they will be very clever if they can guess your table structure
from that description.

Customers
(
ID, (Assumed)
TotalOrders
)

Orders
(
CustomerID (maybe??),
Amount (now I am really guessing),
TotalOrders
)

Give me a hand here? I would have thought there would be a 1..n
between Customers and Orders, so I don't see why orders would have a
TotalOrders field at all unless you have a denormalised structure.

Provide the table structure or at least the relevant fields, and your
pseudo code (Delphi for loop is fine) and we can check if there is a
way to do it in a single statement without a stored procedure.

> Important: I need this solution like SQL statment no StoredProcedure!

We are not up to that, not even sure where the information is coming
from yet. Why don't you want to use a stored procedure by the way?

You could perhaps use triggers to maintain the "total", either by
simply doing an update on the customers table on before insert or
before delete if concurrency is not going to be an issue, or by
creating a summing table if you need to allow multiple orders for the
same customer (simultaneously in separate transactions avoiding lock
conflicts).

Adam