simple Update statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

x-rays - 18 Jul 2006 09:49 GMT

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!

Reply to this Message

Allen Browne - 18 Jul 2006 11:44 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello all,
>
[quoted text clipped - 14 lines]
>
> Thanks in advance!

Reply to this Message

x-rays - 18 Jul 2006 12:11 GMT

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
[quoted text clipped - 23 lines]
> >
> > Thanks in advance!

Reply to this Message

Rick Brandt - 18 Jul 2006 12:40 GMT

> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Allen Browne - 18 Jul 2006 13:15 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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.
[quoted text clipped - 25 lines]
>> >
>> > Thanks in advance!

Reply to this Message