Search code examples
sqlpostgresqlquery-optimization

SQL: delete records from the table A when id is less than equal to maximum id of a group in table B

I have 2 tables arh and ar .I need to delete records from arh table for each accountMappingId where actionRequestId is <= maximum actionRequestId for the operation 'RESTORE' on the equivalent mappingId in ar table. The records should be deleted only in arh table without affecting ar table.

There can be different accountMappingId (1001, 1002, 1003).

-- create table --

create table arh (
  accountMappingId TEXT,
  actionRequestId integer,
  actionrequesthistoryid integer
);

CREATE TABLE ar (
  mappingId TEXT,
  actionRequestId integer,
  operation TEXT
);

-- insert records ---

INSERT INTO arh VALUES ('1001', 1, 1);
INSERT INTO arh VALUES ('1001', 1, 2);
INSERT INTO arh VALUES ('1001', 2, 1);
INSERT INTO arh VALUES ('1001', 2, 2);
INSERT INTO arh VALUES ('1001', 3, 1);
INSERT INTO arh VALUES ('1001', 3, 2);
INSERT INTO arh VALUES ('1001', 4, 1);
INSERT INTO arh VALUES ('1001', 4, 2);
INSERT INTO arh VALUES ('1001', 5, 1);
INSERT INTO arh VALUES ('1001', 5, 2);
INSERT INTO arh VALUES ('1001', 6, 1);
INSERT INTO arh VALUES ('1001', 6, 2);
INSERT INTO arh VALUES ('1001', 7, 1);
INSERT INTO arh VALUES ('1001', 7, 2);
INSERT INTO arh VALUES ('1001', 8, 1);
INSERT INTO arh VALUES ('1001', 8, 2);
INSERT INTO arh VALUES ('1002', 1, 1);
INSERT INTO arh VALUES ('1002', 1, 2);
INSERT INTO arh VALUES ('1002', 2, 1);
INSERT INTO arh VALUES ('1002', 2, 2);
INSERT INTO arh VALUES ('1002', 3, 1);
INSERT INTO arh VALUES ('1002', 3, 2);


INSERT INTO ar VALUES ('1001', 1, 'COPY');
INSERT INTO ar VALUES ('1001', 2, 'REMOVE');
INSERT INTO ar VALUES ('1001', 3, 'RESTORE');
INSERT INTO ar VALUES ('1001', 4, 'COPY');
INSERT INTO ar VALUES ('1001', 5, 'REMOVE');
INSERT INTO ar VALUES ('1001', 6, 'RESTORE'); --> max(actionrequestId) for 'RESTORE' operation in '1001'
INSERT INTO ar VALUES ('1001', 7, 'COPY');
INSERT INTO ar VALUES ('1001', 8, 'REMOVE');
INSERT INTO ar VALUES ('1002', 1, 'COPY');
INSERT INTO ar VALUES ('1002', 2, 'REMOVE');
INSERT INTO ar VALUES ('1002', 3, 'RESTORE'); --> max(actionrequestId) for 'RESTORE' operation in '1002'

Solution what I have tried but can we optimize the delete query?

delete from arh 
where (arh.actionRequestId, arh.accountMappingId) in (
select arh.actionRequestId, new_table.mappingId
from arh 
inner join (
  SELECT mappingId, max(actionrequestid) as actionrequestid
  FROM ar 
  where operation = 'RESTORE'
  group by mappingid
  ) 
as new_table
on arh.accountMappingId = new_table.mappingId
and arh.actionRequestId <= new_table.actionRequestId
order by arh.actionRequestId, new_table.mappingId)
Solution

  • Use a subquery to get the Mapping ID's maximum RESTORE request ID.

    DELETE FROM arh
WHERE actionRequestId <= ( SELECT MAX(ar.actionRequestId) 
                           FROM ar
                           WHERE ar.operation = 'RESTORE' 
                           AND ar.mappingId = arh.accountMappingId );

    Demo: https://dbfiddle.uk/Frx1Ea3_