Updatability Restrictions for Multiple-Table Queries

To be fully updatable, a query must meet several requirements:

You must specify an explicit inner or outer join between tables. Joins created implicitly in the WHERE clause of the SELECT statement aren’t updatable. For example, the following join isn’t updatable:
```
SELECT 
	Products.ProductID, 
	Products.ProductName, 
	Categories.CategoryID, 
	Categories.CategoryName
FROM Categories, Products
WHERE Products.CategoryID = Categories.CategoryID;
```
Summary (GROUP BY), UNION, DISTINCT, and crosstab queries are never updatable. Queries joined to one or more summary queries aren’t updatable, even if you don’t attempt to modify fields from an otherwise updatable table. However, a query may be updatable if it refers to a summary query in a sub-SELECT statement, as in the following example:
```
SELECT Orders.*
FROM Orders
WHERE Orders.Freight >
	(SELECT Avg(Orders.Freight) AS AvgOfFreight
	FROM Orders;);
```
In this case, fields from the Orders table are updatable.
To be able to insert new records into a table in any query, all primary key fields must be present.
While you’re updating a single record in a query, changes to certain fields may render certain other fields nonupdatable until the edit to the record is either saved or canceled. As soon as the user edits data on the “one” side of a query, the join key on the “many” side can no longer be modified. Usually, the “many” side’s join key is updatable. However, because data on the “one” side was modified first, this field is temporarily rendered unmodifiable because row fix-up would discard changes to the “one” side’s data. As soon as the change to the “one” side of the query is saved or canceled, the “many” side’s join key becomes updatable again.
A change to a multiple-table query must not create orphaned records. You can change the join key in the “many” table to a value already present in the “one” table, but you can’t specify a nonexistent value, except in the case of outer joins.