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?
metalteckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You have used apc as alias twice, and so there is no relation with the delete itself. Removing
   prod.apcinvoice apc,
from the subselect would help with that.
But because it is an outer join you do not add any more restriction than the date:
delete from prod.apcinvoice apc from apc.invoice_dte = TO_DATE('2018-01-29', 'YYYY-MM-DD')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
don't use the delete table in the exists from

delete from prod.apcinvoice apc
WHERE EXISTS 
  (SELECT *
from 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'));

Open in new window

0
Mark GeerlingsDatabase AdministratorCommented:
What is the value in doing a multi-table join in your sub-query when those join conditions are all outer joins?  That means: whether a matching record exists or not in the other table, the record in the apc table should still be deleted.  And, since the only filter is on the "apc.invoice_dte" column, it looks to me like your delete could/should simply be:
delete from prod.apcinvoice apc
where apc.invoice_dte = TO_DATE('2018-01-29', 'YYYY-MM-DD'));

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

metalteckAuthor Commented:
@Mark, I understand your point, but that would leave all the records from the other 2 tables that I would want to delete.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
1
Mark GeerlingsDatabase AdministratorCommented:
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.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.