I have a very specific operation I need to apply on a table to fix some data and remove some records.

There is a master table Invoice. Then, I have child records in InvoiceDetail.

We need to rollback a procedure that was done a few months ago because the requirements have been reversed.

Basically, the InvoiceDetail records contain many records related to an Invoice and they have a NoStatus field.

For every Invoice, I need to remove the last InvoiceDetail record if its NoStatus=7 but only if the previous InvoiceDetail record has NoStatus=3.

I have been looking at various alternatives but I haven't found one that was conclusive so far.

Anyone would have an idea of what kind of approach could be used here?