Board index » delphi » Help needed with SQL - Update Statement

Help needed with SQL - Update Statement

Hi All,

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.

--
Arnold Johnson

 

Re:Help needed with SQL - Update Statement


On Fri, 24 Sep 1999 20:02:29 +0100, Arnold Johnson

Quote
<Arno...@win-pro.demon.co.uk> wrote:
>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.

Absolute success for an operation like this can depend on the particular
database type you are using -- something you ommitted.

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.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Re:Help needed with SQL - Update Statement


Hi Steve Koterski,
you are a star. I didn't know that one could construct such update
statements like the one you posted. You have (if it works) just solved a
major problem I had/have.

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

Quote
>Absolute success for an operation like this can depend on the particular
>database type you are using -- something you ommitted.

I am using Advantage's TADSQuery against dBase/Clipper tables. And I
think the concept is based on streamline SQL, whatever that means.
That aside, the whole thing would be used with ADO after porting, so I
am trying to accustom myself with using SQL statements and queries
especially when we port to Client/Server solutions using MS SQL , Oracle
and the like and even Interbase, with which I practice a lot.

Quote

>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))

WOW!
>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.

>_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
>Steve Koterski              "Health nuts are going to feel stupid someday,
>Felton, CA                  lying in hospitals dying of nothing."
>                                                              -- Redd Foxx

--
Arnold Johnson

Re:Help needed with SQL - Update Statement


On Sat, 25 Sep 1999 00:56:44 +0100, Arnold Johnson

Quote
<Arno...@win-pro.demon.co.uk> wrote:
>you are a star. I didn't know that one could construct such update
>statements like the one you posted. You have (if it works) just solved a
>major problem I had/have.

Post a follow-on to the newsgroup if you have continuing problems.

Quote
>In article <37edf03f.21570...@news.gte.net>, Steve Koterski
><koter...@NOSPAMgte.net> writes
>>Absolute success for an operation like this can depend on the particular
>>database type you are using -- something you ommitted.

>I am using Advantage's TADSQuery against dBase/Clipper tables. And I
>think the concept is based on streamline SQL, whatever that means.
>That aside, the whole thing would be used with ADO after porting, so I
>am trying to accustom myself with using SQL statements and queries
>especially when we port to Client/Server solutions using MS SQL , Oracle
>and the like and even Interbase, with which I practice a lot.

Mmm. So is this just a replacement query component? Or does it also involve
a database engine that replaces the BDE?

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.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Other Threads