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

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)

CodePudding user response：

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_

CodePudding user response：

You could find the max(actionRequestId) from ar table where operation = 'RESTORE', then use the USING clause to delete the desired rows from arh table.

delete from arh T
using 
(
  select mappingId, max(actionRequestId) max_actionRequestId
  from ar
  where operation = 'RESTORE'
  group by mappingId
) D
where T.accountMappingId = D.mappingId and
   T.actionRequestId <= D.max_actionRequestId

See demo