Deleting duplicate records from a table which has a child record with foreign key in sql oracle-CodePudding

I have 2 tables. Table A and Table B.

Table A has the following columns: ID, Account_Number, Party_Number, time.

Table B has a foreign key with table A. The columns in Table B are: ID, time, Agreement.

I will be receiving huge data at once in table A and sometimes there will be duplicates inserted into table A (Newly added records will not have any link with table B).

I tried to delete the duplicates using the below 2 queries:

Query 1:

delete from A
where rowid IN (
  select rid
  from (
    select rowid rid, 
      row_number()
        over (partition by account_number,party_number
          order by rowid) rn
    from A
  )
  where rn <> 1
);

Query 2:

delete
from A a
where rowid>(
  select min(rowid)
  from A b
  where b.account_number=a.account_number
    and b.party_number=a.party_number
);

In both cases I am receiving the below error:

Error report - ORA-02292: integrity constraint (B_FK) violated - child record found.

Looking at the error I can understand that it is trying to delete the earlier records which are already linked to table B. What modifications do I need to do on the delete query so it deletes only newly added duplicate records?

Edit: time stores sysdate at the time of insertion. Column ID links table A and table B.

CodePudding user response：

Newly added records will not have any link with table B. What modifications do I need to do on the delete query so it deletes only newly added duplicate records?

One option is to include NOT EXISTS

DELETE FROM A a
      WHERE     ROWID > (SELECT MIN (ROWID)
                           FROM A b
                          WHERE     b.account_number = a.account_number
                                AND b.party_number = a.party_number)
            AND NOT EXISTS
                   (SELECT NULL
                      FROM table_b x
                     WHERE x.id = a.id   --> or whichever columns join tables A and B;
                   );                    --  you never said it so I'm just guessing

CodePudding user response：

You maybe having problems with the pseudocolumn rowid. I'm not sure if it tells us the correct order the rows were inserted.

Instead, you can try the following:

delete A
where ID not in (
  select ID
  from B
) and ID not in (
  select id
  from (
    select id,
      row_number() over (partition by account_number, party_number order by id) rn
    from a
  ) where rn<>1
);

The first not in condition excludes from deletion those rows already in table B (based on the foreign key ID). This should remove the error.

The second not in condition excludes from deletion those rows inserted first for each group of account_number and party_number duplicates. Since you said ID is a sequence, then that's the way to know which row was inserted first. We excludes them because even though they maybe not linked to table B, you may be linking them in table B later.