I wish to "update" a table with info from another table, but do not know
how to write the SQL statement to use with a Query component.
e.g.
Table1 has columns (REF, InQty, OutQty, TotQty, TotValue, AverageCost)
Table2 has columns (REF, COLOUR, QtyIn, QtyOut, QtySold, TotalQty,
TotalValue)
basically Table2 is a break down of table2 into its individual
constituents REF, Colour.
The idea is, to recalculate values from Table2 and update table 1.
Table1 should have the following results for each similar REF column
Table1.InQty       = Sum(Table2.QtyIn),
Table1.OutQty      = Sum(Table2.QtyOut),
Table1.TotQty      = Sum(Table2.TotalQty),
Table1.TotValue    = Sum(Table2.TotalValue),
Table1.AverageCost = Sum(Table2.TotalValue) / Sum(Table2.TotalQty)
where (Sum(Table2.TotalQty) <> 0) and Table1.REF = Table2.Ref
In this example I have used 2 tables that have different field names,
but I do have scenarios where the column names are the same in both
tables.
Help any one?

Thanks in advance.

Local SQL (in 32-bit BDE only) supports using a SELECT subquery as the
source of update atoms for the UPDATE and INSERT statements. If the
structures of the source result set (from the subquery) and the target base
table are not the same, you simply need to provide explicit columns lists
in the UPDATE and SELECT clauses. When data types differ between the source
and target datasets, use the SQL function CAST to force a conversion of the
update atom to match the column in the target table. For each column in the
UPDATE clause, you would need a separate SELECT subquery to produce its
update atom. For example:

  UPDATE Table1 T1
  SET
    InQty =
      (SELECT SUM(T2.QtyIn)
      FROM Table2 T2
      WHERE (T1.REF = T2.Ref)
      HAVING (SUM(T2.TotalQty) != 0)),
    OutQty =
      (SELECT SUM(T3.QtyOut)
      FROM Table2 T3
      WHERE (T1.REF = T3.Ref)
      HAVING (SUM(T3.TotalQty) != 0)),
    TotQty =
      (SELECT SUM(T4.TotalQty)
      FROM Table2 T4
      WHERE (T1.REF = T4.Ref)
      HAVING (SUM(T4.TotalQty) != 0)),
    TotValue =
      (SELECT SUM(T5.TotalValue)
      FROM Table2 T5
      WHERE (T1.REF = T5.Ref)
      HAVING (SUM(T5.TotalQty) != 0)),
    AverageCost =
      (SELECT SUM(T6.TotalValue) / SUM(T6.TotalQty)
      FROM Table2 T6
      WHERE (T1.REF = T6.Ref)
      HAVING (SUM(T6.TotalQty) != 0))

Alternately, use your SELECT statement as the source for  aTBatchmove
component. Represent the target table with a TTable. Use a Mode property
value of batUpdate, map the fields from source to target in the Mappings
property, and effect the update by calling the TBatchmove.Execute method.

In article <37edf03f.21570...@news.gte.net>, Steve Koterski
<koter...@NOSPAMgte.net> writes

What I posted applies to database engines compliant with the SQL-92
standard and (in this case, anyway) local SQL. (Local SQL is the name of
the BDE's implementation of SQL that it uses with dBASE, Paradox, and
FoxPro tables.) It is very possible that your custom tool uses an entirely
different implementation of SQL and finds what I posted unacceptable. In
that case, check the Advantage documentation to determine just what is and
is not supported.