Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle Delete Query  syntax

Posted on 2014-12-18
4
631 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
ID: 40507451
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
ID: 40507767
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
ID: 40507778
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
ID: 40507782
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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