• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 156
  • Last Modified:

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?
0
metalteck
Asked:
metalteck
3 Solutions
 
QlemoBatchelor, 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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
QlemoBatchelor, 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now