simple Update statement

[x-rays]

Hello all,

I have a simple Update statement,

Update [To Append] INNER JOIN [MAPPINGS_DISTINCT] ON [To Append].Make =
[MAPPINGS_DISTINCT].Make SET [To Append].Make =
[MAPPINGS_DISTINCT].MappMake
Where [To Append].Type = "G";

[To Append] is the table I want to update and MAPPINGS_DISTINCT is a
query with unique values. When I push the button "Datasheet View" to
view the rows will affect, works fine. When Run the statement
"exclamation mark" to update the rows, a message appears: "Operation
must use an updateable query", but this is an update query.

Can't figure out what's going wrong. I appreciate any help.

Thanks in advance!

 

[Allen Browne]

The query statement looks okay, but you say that MAPPINGS_DISTINCT is a
query? It it an updatable query? If not the query built on top of it will
not be.

 

[x-rays]

You mean that I can have a select query which is updatable? Not sure I
understand that.

MAPPINGS_DISTINCT query is like that:

Select DISTINCT Make, MappMake from MAPPINGS

The query statement looks okay, but you say that MAPPINGS_DISTINCT is a
query? It it an updatable query? If not the query built on top of it will
not be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all,

I have a simple Update statement,

Update [To Append] INNER JOIN [MAPPINGS_DISTINCT] ON [To Append].Make =
[MAPPINGS_DISTINCT].Make SET [To Append].Make =
[MAPPINGS_DISTINCT].MappMake
Where [To Append].Type = "G";

[To Append] is the table I want to update and MAPPINGS_DISTINCT is a
query with unique values. When I push the button "Datasheet View" to
view the rows will affect, works fine. When Run the statement
"exclamation mark" to update the rows, a message appears: "Operation
must use an updateable query", but this is an update query.

Can't figure out what's going wrong. I appreciate any help.

Thanks in advance!

 

[Rick Brandt]

You mean that I can have a select query which is updatable? Not sure I
understand that.

MAPPINGS_DISTINCT query is like that:

Select DISTINCT Make, MappMake from MAPPINGS

Access requires that ALL tables and queries in an Update query be editable in
their own right even if the update query is not trying to update fields in the
one that is not editable.

Since MAPPINGS_DISTINCT is not editable then your entire update query fails.
Using a sub-query or In(SELECT...) clause instead of a join can often work
around this issue.

 

[Allen Browne]

A SELECT query can be updatable, e.g.:
SELECT * FROM Table1;

Your MAPPINGS_DISTINCT query is not updatable, because it uses the DISTINCT
keyword. Therefore, when you use that query as the source "table" for
another query, the upper level query is not updatable either.

Here's a list of things that may help you identify what will results in a
non-updatable query:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You mean that I can have a select query which is updatable? Not sure I
understand that.

MAPPINGS_DISTINCT query is like that:

Select DISTINCT Make, MappMake from MAPPINGS

The query statement looks okay, but you say that MAPPINGS_DISTINCT is a
query? It it an updatable query? If not the query built on top of it will
not be.

Hello all,

I have a simple Update statement,

Update [To Append] INNER JOIN [MAPPINGS_DISTINCT] ON [To Append].Make =
[MAPPINGS_DISTINCT].Make SET [To Append].Make =
[MAPPINGS_DISTINCT].MappMake
Where [To Append].Type = "G";

[To Append] is the table I want to update and MAPPINGS_DISTINCT is a
query with unique values. When I push the button "Datasheet View" to
view the rows will affect, works fine. When Run the statement
"exclamation mark" to update the rows, a message appears: "Operation
must use an updateable query", but this is an update query.

Can't figure out what's going wrong. I appreciate any help.

Thanks in advance!