[SOLVED] For every row in table x does its identity column also exist in table y

For every row in table x does its identity column also exist in table y

For every row in table x, insert a correct value into table y; each table will have guid's that match, but table x has some bogus name values.

Table x

field 1     field 2
--------------------
good name   guid 1
good name   guid 2

Table y

field 1    field 2
-------------------
bad name   guid 1
bad name   guid 2

Desired output

field 1    field 2
-------------------
good name  guid 1
good name  guid 2

edit: this is the statement I believe I need (thanks Bohemian):

UPDATE parts SET sheetname = (select sheetname from sheets where sheets.sheetid = parts.sheetid)

but I am getting the dreaded "operation must use an updateable query" error. The typical cause of which is not having write access which I do have (and I have other update and insert queries which work fine). both tables have a primary key which is not involved in the query. So I am at a loss as to the error. Tried using "distinctrow" also to no avail.

Solution

Access treats your UPDATE as non-updatable because it contains a subquery.

Since you reported the sheets.sheetid values are unique, I don't see why you need a subquery. Just base your UPDATE on an INNER JOIN between the two tables like this:

UPDATE parts INNER JOIN sheets ON parts.sheetid = sheets.sheetid
SET parts.sheetname = sheets.sheetname