Oracle SQL Delete with multiple tables
I am trying to write a delete statement that will remove all the joined records from the following query:
delete from prod.apcinvoice apc
WHERE EXISTS
(SELECT *
from prod.apcinvoice apc,
prod.apcdistrib apd,
prod.apcattach att
where apc.company = apd.company (+)
and apc.vendor = apd.vendor (+)
and apc.edi_nbr = apd.edi_nbr (+)
and apc.invoice = apd.invoice (+)
and apc.suffix = apd.suffix (+)
and apc.company = att.company (+)
and apc.vendor = att.vendor (+)
and apc.invoice = att.invoice (+)
and apc.invoice_dte = TO_DATE('2018-01-29', 'YYYY-MM-DD'));
When I executed the nested query, I get a total of 538 records.
But when I execute everything, I get a total of 650.
What am I missing to delete the correct amount of records?
delete from prod.apcinvoice apc
WHERE EXISTS
(SELECT *
from prod.apcinvoice apc,
prod.apcdistrib apd,
prod.apcattach att
where apc.company = apd.company (+)
and apc.vendor = apd.vendor (+)
and apc.edi_nbr = apd.edi_nbr (+)
and apc.invoice = apd.invoice (+)
and apc.suffix = apd.suffix (+)
and apc.company = att.company (+)
and apc.vendor = att.vendor (+)
and apc.invoice = att.invoice (+)
and apc.invoice_dte = TO_DATE('2018-01-29', 'YYYY-MM-DD'));
When I executed the nested query, I get a total of 538 records.
But when I execute everything, I get a total of 650.
What am I missing to delete the correct amount of records?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That is a misunderstanding on your side, metalteck. You are just querying different tables, not delete their content. A delete statement always acts on a single table (or view) only.
If these tables have a foreign key relationship defined with "on delete cascade" specified in the foreign keys, then Oracle will automatically delete the related child table records when the parent table records are deleted.
If there are no foreign keys defined between these tables, then Qlemo's comment is correct: an Oracle "delete..." statement affects one table only. You then have to do separate "delete..." statements for each table that you want to delete records from.
If there are no foreign keys defined between these tables, then Qlemo's comment is correct: an Oracle "delete..." statement affects one table only. You then have to do separate "delete..." statements for each table that you want to delete records from.
ASKER