asked on

Operation Must Updateable Query Error

I have the following SQL and it fails with "Operation must use Updateable Query". How can I update the data in the table I want with the type of joins without getting the error message?:

UPDATE ((dbo_CD_POLICY LEFT JOIN dbo_CD_PROJECT ON dbo_CD_POLICY.policy_id = dbo_CD_PROJECT.policy_id) LEFT JOIN dbo_CD_SITE ON dbo_CD_PROJECT.project_id = dbo_CD_SITE.project_id) LEFT JOIN dbo_CD_WELL ON dbo_CD_SITE.site_id = dbo_CD_WELL.site_id SET dbo_CD_WELL.tight_group_id = "YA8x3"
 
WHERE (((dbo_CD_POLICY.customer_name) Like "Canada V*") AND ((dbo_CD_WELL.tight_group_id)="VZoQU"));

Open in new window

Daniel Wilson

Please try:

UPDATE dbo_CD_WELL SET tight_group_id = "YA8x3"
 
From ((dbo_CD_POLICY LEFT JOIN dbo_CD_PROJECT ON dbo_CD_POLICY.policy_id = dbo_CD_PROJECT.policy_id) LEFT JOIN dbo_CD_SITE ON dbo_CD_PROJECT.project_id = dbo_CD_SITE.project_id) LEFT JOIN dbo_CD_WELL ON dbo_CD_SITE.site_id = dbo_CD_WELL.site_id 
 
WHERE (((dbo_CD_POLICY.customer_name) Like "Canada V*") AND ((dbo_CD_WELL.tight_group_id)="VZoQU"));

Open in new window

ASKER CERTIFIED SOLUTION

carazuul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

travisjbennett

Ok, but how do I update only the records with matching primary keys? The two primary keys that should be the match between the two tables for the update command to know which field to update from are

(ModelTaskDataLocal.ModelData_PM_RQ_NUMBER = [qryModelDataPPQAliased].[PM_RQ_NUMBER])
AND
(ModelTaskDataLocal.ModelData_MODEL_WO_NBR = [qryModelDataPPQAliased].[E_MODEL_WO_NUMBER])

travisjbennett

Oops, wrong chat.