How to remove duplicates with one query in h2

Question

I need to delete duplicate records for a specific field (instrument_id), leave only the very first record by id.

Using the MySQL example, this query looks like this:

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

example of creating a table:

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

But when using the H2 or hsqldb database this query cannot be executed, indicating a syntax error.

Is there some way to accomplish duplicate removal similar to the above query, but so that it can be done on h2 or hsqldb database?

Answer 1

A simple query like this should do the trick:

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 )

EDIT: Since it appears you have some syntax errors, here's what I've tested on a h2 web console (ver. 1.4.200).

Create Table

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

Populate Table with data

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);

Select output

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

Run Delete

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 );

Select Output after delete

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