Re: What's wrong with this UPDATE stmt?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

> The FROM clause used here creates a cross product.

is that the same as a cartesian product?

> When combined as shown
> with a WHERE clause between the two tables, the result is identical to an
> INNER JOIN. Indeed, this is an old version of how INNER JOINs are
written.

thanks, Tom, learn something new every day here (not a hard assignment when
it comes to me! <g>).
and i do agree with you that adding the Customer name to the Orders table is
a violation of normalization rules. :)


"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:ewmo%23Yl7FHA.2012@xxxxxxxxxxxxxxxxxxxxxxx
> Hello, tina:
>
> The FROM clause used here creates a cross product. When combined as shown
> with a WHERE clause between the two tables, the result is identical to an
> INNER JOIN. Indeed, this is an old version of how INNER JOINs are
written.
>
> The duplication of CustomerName in the Orders table is likely a violation
of
> the Rules of Normalization. Better that not be in the Orders table at
all.
> A join on CustomerID should be used to retrieve the CustomerName from the
> Customers table.
>
> Tom Ellison
>
>
> "tina" <nospam@xxxxxxxxxxx> wrote in message
> news:rIagf.138566$zb5.4680@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > does "Orders o" mean that you're aliasing the Orders table to "Orders As
> > o"?
> > and ditto "Customers c" to "Customers As c"? and are the two tables not
> > joined in the query, with a LEFT JOIN, RIGHT JOIN, OR INNER JOIN?
> >
> > hth
> >
> >
> > "Tony Lin" <tony@xxxxxxxxxxxxx> wrote in message
> > news:VvudnV54ectRsRzenZ2dnUVZ_sqdnZ2d@xxxxxxxxxxxxxx
> >> I'm looking for help as to why the following UPDATE statement won't
> > execute
> >> in Access:
> >>
> >> UPDATE Orders
> >> SET CustomerName = c.CustomerName
> >> FROM Orders o, Customers c
> >> WHERE o.CustomerID=c.CustomerID;
> >>
> >> It results in the following error message in Access:
> >>
> >> Syntax error (missing operator) in query expression 'c.CustomerName
> >> FROM
> >> Orders o'
> >>
> >> CustomerID is the primary key of the Customers table.
> >>
> >> Tony
> >> Fremont, CA
> >>
> >>
> >
> >
>
>


.

Relevant Pages

  • Re: Sql Northwind DB
    ... Provide a SQL statement and sample result set that will ... list all Customers within the CA state. ... count the total orders for all Customers within the CA ... (Products INNER JOIN ((Employees INNER JOIN ...
    (microsoft.public.sqlserver.mseq)
  • Re: Employees Pay Rate
    ... Now, for an INNER JOIN, you start with a CROSS JOIN, but you keep only the ... records where the ON clause evaluates to FALSE or NULL). ... FROM (tableOne As a INNER JOIN tableTwo As b ... GREATER the dateNewSal actually pointed by the second hand. ...
    (microsoft.public.access.queries)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... if the where clause is on an indexed column I'd ... be *hugely* surprised if the inner join were fully evaluated by SQL ... select distinct c.* from customers c inner join orders o ... The second query has less reads than the first query. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Employees Pay Rate
    ... Now, for an INNER JOIN, you start with a CROSS JOIN, but you keep only the ... records where the ON clause evaluates to FALSE or NULL). ... FROM (tableOne As a INNER JOIN tableTwo As b ... GREATER the dateNewSal actually pointed by the second hand. ...
    (microsoft.public.access.queries)
  • Re: update query: still having problems
    ... Only the records from Department that have a rate value present in sheet1, ... That is how an INNER JOIN works when there is no duplicated values (in one ... you add an extra WHERE clause. ... Your code throws up a syntax error: ...
    (microsoft.public.access.queries)