如何解决SQL更新报错，通过统计tbl_DUMP行数更新tbl_SUMMARY字段？

问题背景 #

两张表信息：

• tbl_SUMMARY：主键为ID_CUSTOMER，包含ADDRESS、TEL、PRINCIPAL、INTEREST字段，需通过统计另一张表的行数更新PRINCIPAL和INTEREST值
• tbl_DUMP：无主键，包含ID_CUSTOMER、DATE、AMOUNT、CODES字段，CODES用于区分"PRINCIPAL"和"INTEREST"，需统计对应类型的行数

原SQL写法触发报错，核心原因是Access Jet SQL不支持直接在SET子句中使用关联子查询作为更新值。

解决方案 #

方法1：使用Access内置DCount函数 #

适合数据量较小的场景，写法简洁直观：

UPDATE tbl_SUMMARY
SET PRINCIPAL = DCount("*", "tbl_DUMP", "ID_CUSTOMER = " & ID_CUSTOMER & " AND CODES='PRINCIPAL'"),
    INTEREST = DCount("*", "tbl_DUMP", "ID_CUSTOMER = " & ID_CUSTOMER & " AND CODES='INTEREST'")

注意：若ID_CUSTOMER为字符串类型，需添加单引号包裹参数：
"ID_CUSTOMER = '" & ID_CUSTOMER & "' AND CODES='PRINCIPAL'"

方法2：先统计再关联更新（推荐，效率更高） #

先分组统计tbl_DUMP中各客户的两类记录行数，再通过JOIN关联更新tbl_SUMMARY：

UPDATE tbl_SUMMARY
INNER JOIN (
    SELECT ID_CUSTOMER,
           SUM(IIF(CODES='PRINCIPAL', 1, 0)) AS PRIN_COUNT,
           SUM(IIF(CODES='INTEREST', 1, 0)) AS INTEREST_COUNT
    FROM tbl_DUMP
    GROUP BY ID_CUSTOMER
) AS DUMP_STATS ON tbl_SUMMARY.ID_CUSTOMER = DUMP_STATS.ID_CUSTOMER
SET tbl_SUMMARY.PRINCIPAL = DUMP_STATS.PRIN_COUNT,
    tbl_SUMMARY.INTEREST = DUMP_STATS.INTEREST_COUNT

方法3：处理无对应记录的客户（设值为0） #

如果需要将tbl_DUMP中无对应记录的客户的PRINCIPAL和INTEREST统一设为0，可使用LEFT JOIN结合NZ函数：

UPDATE tbl_SUMMARY
LEFT JOIN (
    SELECT ID_CUSTOMER,
           SUM(IIF(CODES='PRINCIPAL', 1, 0)) AS PRIN_COUNT,
           SUM(IIF(CODES='INTEREST', 1, 0)) AS INTEREST_COUNT
    FROM tbl_DUMP
    GROUP BY ID_CUSTOMER
) AS DUMP_STATS ON tbl_SUMMARY.ID_CUSTOMER = DUMP_STATS.ID_CUSTOMER
SET tbl_SUMMARY.PRINCIPAL = NZ(DUMP_STATS.PRIN_COUNT, 0),
    tbl_SUMMARY.INTEREST = NZ(DUMP_STATS.INTEREST_COUNT, 0)

错误原因 #

Access Jet查询引擎对更新查询的语法支持有限，即使逻辑上关联子查询会返回单一值，引擎仍会判定该查询为不可更新类型，从而抛出"Operation Must Use an Updateable Query"错误。

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