Please help on this UPDATE Query.

[PKJ]

I have two tables Lt_Offices and Lt_Designation both having a field named RankLevel. I want to update Lt_Offices.RankLevel from the values of Lt_Designations.RankLevel. I have tried following two variants of the UPDATE query but Access pops the message - "Operation must be an updateable query."

These queries are not working -
1.
UPDATE Lt_Offices
SET Lt_Offices.RankLevel = (SELECT Lt_Designations.RankLevel
FROM Lt_Offices LEFT JOIN Lt_Designations
ON Lt_Offices.DesigCodeOfHolder = Lt_Designations.DesigRank
WHERE Lt_Designations.DesigRank = Lt_Offices.DesigCodeOfHolder);


2.
UPDATE Lt_Offices SET Lt_Offices.RankLevel = (Select Lt_Designations.RankLevel FROM Lt_Designations WHERE Lt_Designations.DesigRank = Lt_Offices.DesigCodeOfHolder)
WHERE EXISTS (Select Lt_Designations.RankLevel FROM Lt_Designations WHERE Lt_Designations.DesigRank = Lt_Offices.DesigCodeOfHolder);

I would appreciate any help on these queries - both are for same purpose but none is working.
Thanks.

[orange]

How are your tables related? What are the Primary and Foreign keys?

This may work

UPDATE Lt_Offices INNER JOIN Lt_Designations ON
Lt_Offices.DesigCodeOfHolder = Lt_Designations.DesigRank
SET Lt_Offices.RankLevel = Lt_Designations.RankLevel

provided that the tables are joined
ON Lt_Offices.DesigCodeOfHolder = Lt_Designations.DesigRank

[PKJ]

Thanks it works now.

[PKJ]

I had not joined the tables - after doing the join as suggested by Orange i.e. ON Lt_Offices.DesigCodeOfHolder = Lt_Designations.DesigRank, the query runs fine. Many thanks to him.T