Convert Oracle SQL select statement to the proper Delete Statement

I'm trying to convert a select statement that is working correctly into a delete statement and I'm not having any luck. I'm trying to delete data from a parent and child table. The child table doesn't always have any child records but if it does then I want the data deleted in both tables if where clause is true. If there are no child records then I still want the parent table records to be deleted.
Here is the select statement that works like I want it to.
SELECT MY_PARENT.ID, MY_PARENT.PRINT_DATE, MY_CHILD_DTL.ID, MY_CHILD_DTL.MY_PARENT_ID
FROM MY_PARENT LEFT JOIN MY_CHILD_DTL ON MY_PARENT.ID = MY_CHILD_DTL.MY_PARENT_ID
WHERE MY_PARENT.PRINT_DATE IS NULL OR MY_PARENT.PRINT_DATE < (SYSDATE - 30)

I need to do the exact same thing with a DELETE COMMAND
dkma2010Asked:
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.

johnsoneSenior Oracle DBACommented:
Do you have a foreign key constraint between the 2 tables?  If so, are cascading deletes turned on?  If cascading deletes are turned on, then just delete the parent and the child will get done for you.

If not, then 2 delete statements are necessary:

DELETE my_child_dtl 
WHERE  my_parent_id IN (SELECT id 
                        FROM   my_parent 
                        WHERE  print_date IS NULL 
                                OR my_parent.print_date < ( SYSDATE - 30 )); 

DELETE my_parent 
WHERE  print_date IS NULL 
        OR my_parent.print_date < ( SYSDATE - 30 ); 

Open in new window

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
dkma2010Author Commented:
Thanks for the info found out that the cascading deletes were on so that took care of the problem. Appreciated the other code also.
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
Oracle Database

From novice to tech pro — start learning today.