Oracle Delete Query syntax

I have a table that holds information about a Page(web page). And on that page it could potentially have different types of objects(Headers, Lines, Paragraphs, etc..). I am trying to clean up the tables and I have the PageIDs I want to delete. Here is the query that brought back the data that I used to find pages that I want to Delete. Unfortunately these tables were not built in a way that when I Delete from the Page table that it doesnt Cascade and delete from the tables that are associated with these pages. So bottom line is I would like to write a query that I can delete the Page and all the associated rows in the other tables. Heres the query I used to get the pages I want to delete and the tables used in the left joins are the tables I need to try and figure out how to delete from as well.

Select p.page_id, p.page_title, pc.section_id, ln.line_id, l.link_id, ll.linklist_id, p.para_id, h.header_id, i.image_id, bl.bulletlist_id, p.para_text, h.header_text, i.image_title, bl.bulletlist_text
from omni_ent_pages p
inner join omni_ent_page_content pc on p.page_id = pc.page_id
left join omni_ent_line ln on pc.section_id = ln.line_id
left join omni_ent_paragraph p on pc.section_id = p.para_id
left join omni_ent_header h on pc.section_id = h.header_id
left join omni_ent_link l on pc.section_id = l.link_id
left join omni_ent_linklist ll on pc.section_id = ll.linklist_id
left join omni_ent_image i on pc.section_id = i.image_id
left join omni_ent_bulletlist bl on pc.section_id = bl.bulletlist_id
order by page_id, pc.section_ordinal;

I am doing this in SQLDeveloper so any help would be appreciated!! Thanks
jknj72Asked:
Who is Participating?
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.

HainKurtSr. System AnalystCommented:
create view pages2delete as (your query here)

then

delete from table1 where pageid in (select pageid from pages2delete)
delete from table2 where pageid in (select pageid from pages2delete)
..
delete from tablen where pageid in (select pageid from pages2delete)

then delete it from master...
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
johnsoneSenior Oracle DBACommented:
There is no where clause in your query, so it will delete all rows.  If that is what you are trying to do, then why not just truncate the tables?  If you mean to have some sort of where clause, add it to this query:
CREATE TABLE omni_ent_pages_delete AS 
SELECT p.page_id, 
       pc.section_id 
FROM   omni_ent_pages p 
join   omni_ent_page_content pc 
ON     pc.secion_id = p.page_id 
WHERE  ....

Open in new window

Then do all the deletes:
DELETE omni_ent_line 
WHERE  line_id IN (SELECT section_id 
                   FROM   omni_ent_pages_delete); 
DELETE omni_ent_paragraph 
WHERE  para_id IN (SELECT section_id 
                   FROM   omni_ent_pages_delete); 
DELETE omni_ent_header 
WHERE  header_id IN (SELECT section_id 
                     FROM   omni_ent_pages_delete); 
DELETE omni_ent_link 
WHERE  link_id IN (SELECT section_id 
                   FROM   omni_ent_pages_delete); 
DELETE omni_ent_linklist 
WHERE  linklist_id IN (SELECT section_id 
                       FROM   omni_ent_pages_delete); 
DELETE omni_ent_image 
WHERE  image_id IN (SELECT section_id 
                    FROM   omni_ent_pages_delete); 
DELETE omni_ent_bulletlist 
WHERE  bulletlist_id IN (SELECT section_id 
                         FROM   omni_ent_pages_delete); 
DELETE omni_ent_page_content 
WHERE  page_id IN (SELECT page_id 
                   FROM   omni_ent_pages_delete); 
DELETE omni_ent_pages
WHERE  page_id IN (SELECT page_id 
                   FROM   omni_ent_pages_delete); 
DROP TABLE omni_ent_pages_delete;

Open in new window

0
jknj72Author Commented:
Not exactly what I was looking for but I couldnt do it the way I was thinking so the view was a good idea.
0
johnsoneSenior Oracle DBACommented:
Be aware that using a view like that to delete records can result in ORA-01555.  Especially if you commit each delete.
0
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.