如何在Access数据库中保留大ID行并删除重复数据

• 数据表：tblCrossReferences（6万+行），包含主键字段ID，已新增IsDeleted字段
• 重复行判定规则：[Mfg part number]、[Manufacturer]、[Substitution]三个字段值完全相同
• 需求：将重复行中ID数值较小的记录的IsDeleted设为1，保留每组重复行中ID最大的记录（例：ID1与ID2重复时保留ID2，ID5与ID6重复时保留ID6）
• 现有问题：尝试的SQL语句无法完成标记操作

用户尝试的SQL语句 #

UPDATE tblCrossReferences
SET IsDeleted = 1
WHERE ID NOT IN
(
SELECT tblCrossReferences.IsDeleted, tblCrossReferences.ID, tblCrossReferences.[Mfg part number], tblCrossReferences.Manufacturer, tblCrossReferences.[Substitution], MAX(tblCrossReferences.ID)
FROM tblCrossReferences
WHERE (((tblCrossReferences.[Mfg part number]) In (SELECT [Mfg part number] FROM [tblCrossReferences] As Tmp GROUP BY [Mfg part number],[Manufacturer],[Substitution],[SKF Alternative Offer] HAVING Count(*)>1  And [Manufacturer] = [tblCrossReferences].[Manufacturer] And [Substitution] = [tblCrossReferences].[Substitution])))
ORDER BY tblCrossReferences.[Mfg part number], tblCrossReferences.Manufacturer, tblCrossReferences.[Substitution]);

问题分析 #

原SQL存在两个核心问题：

1. NOT IN子查询返回多列数据，但NOT IN仅支持单一列匹配（需仅返回ID值）
2. 分组条件错误加入[SKF Alternative Offer]字段，导致重复行判定逻辑偏离需求

解决方案 #

方案1：简洁版SQL（优先尝试） #

通过分组获取每组重复记录的最大ID，将不在该集合内的记录标记为待删除：

UPDATE tblCrossReferences
SET IsDeleted = 1
WHERE ID NOT IN (
    SELECT MAX(ID)
    FROM tblCrossReferences
    GROUP BY [Mfg part number], [Manufacturer], [Substitution]
)

方案2：兼容Access查询引擎的版本（若方案1报错） #

使用EXISTS子查询规避Access对NOT IN的兼容性问题，逻辑更稳定：

UPDATE tblCrossReferences AS t
SET IsDeleted = 1
WHERE NOT EXISTS (
    SELECT 1
    FROM (
        SELECT 
            [Mfg part number], 
            [Manufacturer], 
            [Substitution], 
            MAX(ID) AS MaxID
        FROM tblCrossReferences
        GROUP BY [Mfg part number], [Manufacturer], [Substitution]
    ) AS grouped
    WHERE t.[Mfg part number] = grouped.[Mfg part number]
      AND t.Manufacturer = grouped.Manufacturer
      AND t.[Substitution] = grouped.[Substitution]
      AND t.ID = grouped.MaxID
)

逻辑说明 #

• 先按重复判定的三个字段分组，每组提取最大的ID（即需要保留的记录ID）
• 将所有不属于该最大ID集合的记录的IsDeleted设为1，完成标记

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