Solved

Oracle Delete Query  syntax

Posted on 2014-12-18
4
622 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Verification of DataPump Export and Import 17 56
Oracle dataguard 5 32
Showing random records from database 10 37
sql server insert 12 30
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Read about achieving the basic levels of HRIS security in the workplace.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

821 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