H2/HSQLDB如何用单条SQL删除instrument_id重复记录并保留最小id
H2/HSQLDB 兼容的 instrument_id 字段去重实现方案
语法不兼容的原因
原MySQL的写法属于数据库私有的多表删除扩展语法,不符合ANSI SQL通用标准,H2、HSQLDB均不支持这种「指定别名直接关联删除」的写法,因此执行时会触发语法解析错误。
两个数据库都可以实现和原逻辑完全对等的单条去重SQL,核心规则和需求保持一致:删除所有同instrument_id分组内id非最小的重复记录,仅保留每组id最小的原始行。
H2 环境可执行单条SQL
H2 会校验DELETE操作的同表子查询引用,直接在IN子查询中关联同表会触发"表正在被操作,禁止同时读取"的错误,需要嵌套一层临时结果集绕过校验,可直接执行:
DELETE FROM instrument_bit_detail WHERE id IN ( SELECT dup_id FROM ( SELECT t1.id AS dup_id FROM instrument_bit_detail t1 INNER JOIN instrument_bit_detail t2 ON t1.instrument_id = t2.instrument_id WHERE t1.id > t2.id ) AS dup_candidates );
HSQLDB 环境可执行单条SQL
HSQLDB 2.x 及以上版本完全支持ANSI SQL的存在性判断语法,这类写法的执行效率比关联匹配更高,也不存在同表引用冲突的问题,可直接执行:
DELETE FROM instrument_bit_detail main WHERE EXISTS ( SELECT 1 FROM instrument_bit_detail ref WHERE ref.instrument_id = main.instrument_id AND ref.id < main.id );
操作前核对建议
正式执行删除语句前,可运行以下两个数据库均兼容的查询语句,核对待删除的记录范围,避免误删数据:
SELECT main.* FROM instrument_bit_detail main WHERE EXISTS ( SELECT 1 FROM instrument_bit_detail ref WHERE ref.instrument_id = main.instrument_id AND ref.id < main.id );
两种适配写法的删除范围和原MySQL实现完全一致,执行完成后每个
instrument_id仅保留按id排序的第一条原始记录。
内容的提问来源于stack exchange,提问作者FreeOnGoo
相关产品推荐
相关产品推荐

