请求将SQL Server更新查询转换为MS Access兼容SQL查询

原SQL Server查询语句 #

update stock
set qty = qty - TotalQty
from (
select Sales_Detial.barcode, sum(Sales_Detial.qty) as TotalQty
from Sales_Detial
group by Sales_Detial.barcode
) as a
where a.barcode = stock.barcode

转换后的MS Access查询语句 #

UPDATE stock 
INNER JOIN (
    SELECT Sales_Detial.barcode, SUM(Sales_Detial.qty) AS TotalQty
    FROM Sales_Detial
    GROUP BY Sales_Detial.barcode
) AS a ON stock.barcode = a.barcode
SET stock.qty = stock.qty - a.TotalQty;

转换关键点 #

• MS Access不支持SQL Server的UPDATE...FROM语法，需改用UPDATE...INNER JOIN关联子查询与目标表
• 必须明确指定字段所属表（如stock.qty、a.TotalQty），避免Access解析歧义
• 聚合函数SUM采用大写格式（符合Access SQL的标准写法）

内容的提问来源于stack exchange，提问作者Gohar Ali