Solved

Oracle Delete Query  syntax

Posted on 2014-12-18
4
608 Views
Last Modified: 2014-12-18
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
0
Comment
Question by:jknj72
  • 2
4 Comments
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
Comment Utility
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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 

Author Closing Comment

by:jknj72
Comment Utility
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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Be aware that using a view like that to delete records can result in ORA-01555.  Especially if you commit each delete.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now