Update query using join

[meboz]

Hi all.

I would like to run an update query to update rows in a table, and update only those rows that appear in another select query.

I could do this using the IN clause with a sub-query but it takes too long.

I would like to do this by joining the table with the query but i seem to get the updateable query error.

Any reason why this occurs and how I might go about this?

ps. Im sick and tired of using the IN clause with sub-queries!!!

Thanks

 

[meboz]

This is the statement that wont execute:

UPDATE EmployeeData INNER JOIN qrySuper_Both ON EmployeeData.[ID Number] = qrySuper_Both.[ID Number] SET EmployeeData.SuperContributions = "both";

This is the statement behind qrySuper_Both:

SELECT qrySuper_Pre.[ID Number]
FROM qrySuper_Post INNER JOIN qrySuper_Pre ON qrySuper_Post.[ID Number] = qrySuper_Pre.[ID Number];


This is the statement behind qrySuper_Pre:

SELECT DISTINCT SUPERData.[ID Number]
FROM SUPERData
WHERE (((SUPERData.[Allowance/Deduction]) In ("9O","9P","9Q")));

This is the statement behind qrySuper_Post;

SELECT DISTINCT SUPERData.[ID Number]
FROM SUPERData
WHERE (((SUPERData.[Allowance/Deduction]) In ("9J","9K","9L","9M","9N")));

Now...

[ID Number] in the table 'SUPERData' is not the primary key.

Perhaps the DISTINCT clause prevents the update??

Thanks Pat

 

[meboz]

Cheers Pat!