java—如何在h2中通过一个查询删除重复项

juud5qan  于 2021-06-30  发布在  Java
关注(0)|答案(1)|浏览(409)

我需要删除特定字段的重复记录( instrument_id ),只留下 id .
使用mysql示例,此查询如下所示:

DELETE t1 FROM instrument_bit_detail t1, instrument_bit_detail t2
WHERE t1.id > t2.id AND
t1.instrument_id = t2.instrument_id;

创建表的示例:

CREATE TABLE `instrument_bit_detail` (
  `id` bigint(20) NOT NULL,
  `instrument_id` bigint(20) NOT NULL,
  `modification_time` datetime DEFAULT NULL
)

但是在使用h2或hsqldb数据库时,无法执行此查询,这表明存在语法错误。
是否有类似于上述查询的方法来完成重复删除,但这样就可以在h2或hsqldb数据库上完成?

mdfafbf1

mdfafbf11#

这样一个简单的查询应该可以做到:

DELETE FROM instrument_bit_detail t1
WHERE t1.id > (SELECT MIN(t2.id) FROM instrument_bit_detail t2 WHERE t1.instrument_id = t2.instrument_id )

编辑:因为看起来你有一些语法错误,下面是我在h2web控制台上测试的内容。1.4.200).

创建表

CREATE TABLE instrument_bit_detail (
  id bigint(20) NOT NULL,
  instrument_id bigint(20) NOT NULL,
  modification_time datetime DEFAULT NULL
);

用数据填充表

insert into instrument_bit_detail(id, instrument_id, modification_time) values(1,10, CURRENT_DATE);
insert into instrument_bit_detail(id, instrument_id, modification_time) values(2,11, CURRENT_DATE);
insert into instrument_bit_detail(id, instrument_id, modification_time) values(3,10, CURRENT_DATE);
insert into instrument_bit_detail(id, instrument_id, modification_time) values(4,12, CURRENT_DATE);
insert into instrument_bit_detail(id, instrument_id, modification_time) values(5,11, CURRENT_DATE);
insert into instrument_bit_detail(id, instrument_id, modification_time) values(6,10, CURRENT_DATE);
insert into instrument_bit_detail(id, instrument_id, modification_time) values(7,12, CURRENT_DATE);

选择输出

ID      INSTRUMENT_ID   MODIFICATION_TIME  
1       10              2020-12-22 00:00:00
2       11              2020-12-22 00:00:00
3       10              2020-12-22 00:00:00
4       12              2020-12-22 00:00:00
5       11              2020-12-22 00:00:00
6       10              2020-12-22 00:00:00
7       12              2020-12-22 00:00:00

运行删除

DELETE FROM instrument_bit_detail t1 WHERE t1.id > (SELECT MIN(t2.id) FROM instrument_bit_detail t2 WHERE t1.instrument_id = t2.instrument_id );

删除后选择输出

ID      INSTRUMENT_ID   MODIFICATION_TIME  
1       10              2020-12-22 00:00:00
2       11              2020-12-22 00:00:00
4       12              2020-12-22 00:00:00

相关问题