Microsoft Access中追加数据时验证复合主键存在性问题求助

你的代码报错主要有两个原因：

• 表名拼写错误：NOT EXISTS子查询里写的productsByProvider少了末尾的s，和主查询里的表名productsByProviders不匹配，Access无法识别这个表/别名。
• 语法括号错误：WHERE子句最后多了一个闭合括号)，破坏了SQL的语法结构。

修正后的完整SQL代码如下：

INSERT INTO percentage ( productId, productName, salesPrice, currentProvider, offerProvider, offerPrice, percentage )
SELECT products.productId AS productId, products.productName AS productName, products.pricePerUnitOrKg AS salesPrice, products.providerId AS currentProvider, productsByProviders.providerId AS offerProvider, productsByProviders.pricePerUnitOrKg AS offerPrice, Round(products.pricePerUnitOrKg/productsByProviders.pricePerUnitOrKg,3) AS percentage
FROM products INNER JOIN productsByProviders ON products.productId = productsByProviders.productId
WHERE NOT EXISTS (
    SELECT 1 FROM percentage 
    WHERE percentage.productId = productsByProviders.productId 
    AND percentage.offerProvider = productsByProviders.providerId
)

另外补充一个Access里常用的替代写法，用LEFT JOIN筛选未匹配的记录，效果和NOT EXISTS一致，有时候兼容性更好：

INSERT INTO percentage ( productId, productName, salesPrice, currentProvider, offerProvider, offerPrice, percentage )
SELECT products.productId, products.productName, products.pricePerUnitOrKg, products.providerId, productsByProviders.providerId, productsByProviders.pricePerUnitOrKg, Round(products.pricePerUnitOrKg/productsByProviders.pricePerUnitOrKg,3)
FROM (products INNER JOIN productsByProviders ON products.productId = productsByProviders.productId)
LEFT JOIN percentage ON (products.productId = percentage.productId AND productsByProviders.providerId = percentage.offerProvider)
WHERE percentage.productId IS NULL

内容的提问来源于stack exchange，提问作者Thunder Coder