如何通过Access查询删除Raw_data表中未在CoCode表存在的行？

Got it, let's break this down for Access—since its Jet SQL has a few specific rules you need to follow to make delete joins work correctly.

First, the core query you'll need: this uses a LEFT JOIN to identify rows in Raw_data that have no matching company code in CoCode, then deletes those rows.

DELETE Raw_data.*
FROM Raw_data
LEFT JOIN CoCode ON Raw_data.CompanyCode = CoCode.CompanyCode
WHERE CoCode.CompanyCode IS NULL;

Key Notes to Avoid Headaches: #

• Always specify Raw_data.*: Access requires you to explicitly target the table you want to delete from in the DELETE clause—omitting this will throw a syntax error.
• Double-check your field names: If your company code fields have different names (e.g., Raw_data uses CoCd while CoCode uses CompanyID), adjust the ON clause to match: ON Raw_data.CoCd = CoCode.CompanyID.
• Validate first before deleting: Never run a delete query blind! First run a SELECT version to confirm you're targeting the right rows:

  SELECT Raw_data.*
  FROM Raw_data
  LEFT JOIN CoCode ON Raw_data.CompanyCode = CoCode.CompanyCode
  WHERE CoCode.CompanyCode IS NULL;
  

• Backup your data: Even with validation, it's smart to make a copy of Raw_data before running the delete—just in case.

That's it! This should clean out all the orphaned rows in Raw_data that don't have a matching company code in your reference table.

内容的提问来源于stack exchange，提问作者Amit Mishra