What's wrong with this UPDATE stmt?

[Tony Lin]

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

 

[tina]

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

 

[Tom Ellison]

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

 

[Pat Hartman\(MVP\)]

You need to use explicit joins with Access or you'll get Cartesian Products
(which are NOT updatable) plus your syntax is all wrong. When in doubt, use
the QBE to build the basic query. You can switch to SQL view later if you
are more comfortable.

UPDATE Orders AS o Inner Join Customers AS c ON o.CustomerID=c.CustomerID
SET o.CustomerName = c.CustomerName;

 

[Tony Lin]

Pat,

Thank you. Your syntax worked great!

BTW, my original syntax came from the SQL Server Books OnLine. I appreciate
that SQL syntax on SQL Server isn't the exactly the same as that on Access,
but the basic structure would be the same.

Here's the exact example copied from my SQL Server2000 Online Help dealing
with the syntax of the UPDATE statement:

UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
Thanks again for your help.TonyFremont, CA"Pat Hartman(MVP)"
You need to use explicit joins
with Access or you'll get Cartesian Products

 

[tina]

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.

 

[Marshall Barton]

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.

I think the problem is that an Update query does not have a
FROM clause. There may also be an issue with the
unqualified CustomerName.

Try this:

UPDATE Orders o INNER JOIN Customers c
ON o.CustomerID=c.CustomerID
SET o.CustomerName = c.CustomerName

But, as Tom said, Why?

 

[Tom Ellison]

is that the same as a cartesian product?

Yup!

Tom Ellison

 

[Guest]

hi,,

i hope the below query will solve the problem...

UPDATE Orders,customers
SET o.CustomerName = c.CustomerName
WHERE o.CustomerID=c.CustomerID;

Thanks

Sunil.T

 

[Guest]

hi,

Update Query Dont have a FROM Clause....Ok

u can use the below query

UPDATE Orders,customers
SET o.CustomerName = c.CustomerName
WHERE o.CustomerID=c.CustomerID;


Thanx

With Regards

Sunil.T

 

[John Vinson]

hi,,

i hope the below query will solve the problem...

UPDATE Orders,customers
SET o.CustomerName = c.CustomerName
WHERE o.CustomerID=c.CustomerID;

Sorry, Sunil, it will not solve the problem.

This is the older SQL-88 syntax, in which tables are joined using the
WHERE clause. Such queries still work for selecting data but are not
updateable.

The newer SQL-92 syntax joins tables using the JOIN clause:

UPDATE Orders o
INNER JOIN Customers c
ON o.CustomerID = c.CustomerID
SET o.CustomerName = c.CustomerName;


John W. Vinson[MVP]

 

[Guest]

Hi John,

i have tried that Query in MS Access 2003 and it worked fine..
i think that query is right...may be ur right but i executed that query
successfully....

Thanks

With regards

Sunil.T

 

[Pat Hartman\(MVP\)]

It does seem to work but why would you want to use old syntax? At some
point Jet will stop supporting it.

 

[Guest]

hi pat,

am not experienced like u..just 1yr b4 only i started working in
Access...may be ur right.but , when i see that question in the forum i tried
in access and in the first attempt itself i got the result..thats y i posted
that query in the forum...

sorry for posting older version of query.

Thanks

Sunil.T