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,

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)
with Access or you'll get Cartesian Products

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

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]