Re: UPDATE with a SELECT - is it possible?

by joining on non-pk fields (a "group field", or could be "group fields"),
a specific summary record may be joined to *one/several/or many*
records in the base table.

In the case of the SUM example, we would start with
setting all summary.SumOfAmount to 0.

For every record in summary table, *all* the records in
basetable are "cycled through" where they match on
the "group" fields, resulting in an aggregate sum.

I'm not saying this is the best way to do this, but
just wanted to illustrate the effect of JOIN
in a "range-to-range" update query if you do
not join on the primary keys.

If you do not join your ranges on pk's
(or join fields that produce only one range record
per only one record in the other range),
then you will be doing the equivalent of
Michel's LAST example.

Hi Gary, Thank you very much for that very thorough reply.
I think I can use the joins in my situation. Great stuff.

Jesper


.

Relevant Pages

  • Re: Trying to optimize a query with a bunch of INNER JOINs
    ... and it's taking just as long as with the sum. ... took almost 2 minutes for the query to come back, ... >> Yes, I do have foreign keys on the main table, as well as primary keys ...
    (microsoft.public.sqlserver.programming)
  • RE: Unwanted Prompts
    ... There are no primary keys set for this table. ... As there are no unique records for this table. ... As I stated this happens with all of my queries that perform ... Sum(RRbyWOSearch.Other) AS SumOfOther, SumAS [Sum ...
    (microsoft.public.access.queries)
  • Re: Date Calculation..urgent please
    ... Since the sample Adventure Works cube already has Date in multiple ... here's a query to illustrate how to sum the Sales Amount due to ...
    (microsoft.public.sqlserver.olap)
  • Re: status of quadruple precision arithmetic in g95 and gfortran?
    ... Sample Program to illustrate DP versus QP compute times: ... end Program Qtime ... Sum= 7.500000080627340E+015 ...
    (comp.lang.fortran)