Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete from multiple tables

Posted on 2013-10-24
28
Medium Priority
?
486 Views
Last Modified: 2013-11-07
I have some tables where when I delete the value from my main table I have to do a cascading type delete. I have a table called OMNI_ENT_PAGE_CONTENT. This table has a column that holds values that are the IDs for rows in different tables.

OMNI_ENT_PAGE_CONTENT
PAGE_ID     SECTIONID         TYPE           ORDINAL
10013         30748                    IMAGE        1
10013         1249                     LINE        2
10013         2408                    HEADER        3
10013         50861                 PARAGRAPH        4
10013         30749                      IMAGE        5
10013         2409                     HEADER        6

The SectionID holds the value for the key in the table that is in the TYPE column
So for the first row, the SectionID(30748) is the Key in the OMNI_ENT_IMAGE table
So I would need to go through every row where Page_ID = 10013 and delete the records in the applicable table with the key in SectionID

The tables that apply to the TYPE column are
OMNI_ENT_IMAGE
OMNI_ENT_LINE
OMNI_ENT_HEADER
OMNI_ENT_PARAGRAPH

Thanks for the help
0
Comment
Question by:jknj72
[X]
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
  • 10
  • 9
  • 8
28 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598381
If you can, set up cascading constraints.  then you delete from the parent table and it will automatically delete all the children.

If you can't use these for some reason, you need to manually delete the children first.
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598388
I'd suggest you use a after delete trigger. then you could perform the cascading deletes manually, like this:

[....]

execute immediate 'delete from OMNI_ENT_' || :old.type ' where <key_col> = ''' || :old.SECTION_ID || ''';

[....]

just a thought ;-)
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598393
slightwv's right: try first to use FKs with casc. constr.
if that could not be applied, then you could try using triggers...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598424
If you go with the triggers, no need for 'execute immediate'.  Just do a straight delete.
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598439
no need for 'execute immediate'.  Just do a straight delete.

In general yes, but what happens if he creates 1 or more child tables?! When doing HC'ed deletes within the trigger, he has to change it everytime a new child tabe comes into play...

Using the more generic exec imm., it doesn't matter (using a cursor iterating over the user_tables starting with 'OMNI_ENT_%'....
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598453
>> he has to change it everytime a new child tabe comes into play

I don't understand the logic here.

If you can do two execute immediate deletes, you should be able to do two regular deletes.

The issue with execute immediate is you lose the dependencies (the user_dependencies view).  Then if you change something in the environment, Oracle cannot invalidate or keep you from doing something 'bad'.
0
 

Author Comment

by:jknj72
ID: 39598480
The parent table is not set up with Cascading Constraints so I will have to manually do this. Also, for me to use a trigger I will need the more info because Ive only used triggers a couple of time for Inserts so its kind of new territory for me.  

Thoughts?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598490
>>The parent table is not set up with Cascading Constraints so I will have to manually do this

Can you not add cascading constraints?

That is the 'correct' way to do this and I would strongly suggest you do it this way.

Here is a sample showing a trigger:
drop table tab2 purge;
drop table tab1 purge;

create table tab1(col1 char(1) primary key);

create table tab2(col1 char(1), col2 char(1),
	constraint tab_fk foreign key (col2) 
		references tab1(col1)
	);

insert into tab1 values('a');

insert into tab2 values('a','a');
commit;

create or replace trigger tab1_del_trig
before delete on tab1
for each row
begin
	delete from tab2 where col2 = :old.col1;
end;
/

show errors

delete from tab1;
rollback;

Open in new window

0
 

Author Comment

by:jknj72
ID: 39598547
If it was my application I certainly would add the constraints but this db has been constructed in the wrong way. There is no normalization and the relations are designed terribly. No primary keys no foreign keys, etc...Just an example

Almost everything exists in on table with the Lookup_Ky column holds the values for different keys by their values. SO if I wanted to select the "News" items I would have to query the table where the Lookup_Key = "NEWS_ITEM" and he has about 10 columns that depending on the key is depending on what goes into certain columns. All columns have very generic names like LOOKUP_CODE and LOOKU_SUBTYPE_CODE. Also, if he doesnt have enough columns to put his values in he puts multiple values into a column with a Pipe delimiter. Its horrible and I want nothing to do with it. Hence I cant just go and modifiy theses tables any way I want to.  So I just need to get this to work and move on.

I do appreciate you guys letting me know the right way to do things and when it is my job to create the db I will def use these tips
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598581
My 2 cent narrative:

I realize this won't go anywhere but I suggest you start training Management on the 'correct' way to do things.  DBAs need to be involved at design time if not throughout development.

I've worked in shops where the DBA wasn't involved.  We were just expected to give the folks 'space' and keep the database up and running.  That was all well and good until things like this happen.  I never 'fixed' things for them.  I just told them how to fix it.

If the DBA isn't allowed to control or at least be involved in the design, then how is it the DBA's 'job' to do the cascading deletes?

Throw the task back to the designers/developers.

Make them learn their lessons:  Tell them it's their job to clean up their own messes.

If the DBA is constantly putting in band-aids/kludge fixes to bail them out, they will never change their behavior.
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598602
If you can do two execute immediate deletes, you should be able to do two regular deletes.

You definitely got me wrong: the trigger should look something like this:

create or replace trigger tab1_del_trig
before delete on tab1
for each row
begin

execute immediate 'delete from OMNI_ENT_' || :old.type ' where <key_col> = ''' || :old.SECTION_ID || ''';

end;

Open in new window

0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598609
Throw the task back to the designers/developers.
HELL YEAH!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598610
>>You definitely got me wrong

Yes I did.  I missed the point about the column name in the table drives the table to delete from!!!

Looks like you do need execute immediate.
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598616
The issue with execute immediate is you lose the dependencies (the user_dependencies view).

yes, in that case it would be some kind of "trade-off": You'd lose a bit dep, but on the other hand you gain more flexibility and less hard-coded SQL within a trigger...
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598622
>>less hard-coded SQL within a trigger...

I suppose.

>>Looks like you do need execute immediate.

I take this back, it does involve changing the trigger if you ever get a new table but you could use a case statement of if-then structure and still not use execute immediate.
0
 

Author Comment

by:jknj72
ID: 39598625
Hey slight heres the kicker, the DBAs arent involved at all. The guy who built the DB is a software developer(consultant) that built everything. Then he brought me on and I have to just follow his lead. Ive tried to tell him but he fights me over everything so I quit letting him know where he went wrong. I too am a consultant and I can only care so much at this point. I do my job and I go home. I usually take more pride in my work but I cant at this point. Im not married to anything, especially this DB.

I will try these triggers tomorrow and let you all know how it went.

Thanks for all your help

JK
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598642
Man, that scenario sucks...  Guess as long as it's a paycheck!!!
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598651
I take this back, it does involve changing the trigger if you ever get a new table but you could use a case statement of if-then structure and still not use execute immediate.

Again, this will work (if or case) with the existing 4 child tables..
But you'd have to change the trigger if another, new child table would be created...

@jknj72: keep calm & your head up ;-) We feel your pain...

Good luck & kind regards,
Alex
0
 

Author Comment

by:jknj72
ID: 39600108
Hey Alex or Slight can one of you guys just explain to me this trigger real quick

create or replace trigger tab1_del_trig
before delete on tab1  -- Im assuming this is my table(OMNI_ENT_PAGE_CONTENT)
for each row
begin

execute immediate 'delete from OMNI_ENT_' || :old.type ' where <key_col> = ''' || :old.SECTION_ID || ''';


What value will be in old.type and <key_col> or any of these values? Im assuming if I call Delete from OMNI_ENT_PAGE_CONTENT Where Page_ID = iPageID this will run? Remember there are alot of rows with the same PAGEID

Im not too far off with understanding this just need a little clarification.

Thanks
JK

end;
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39600127
:old.type can be one of these values: 'IMAGE', 'LINE', 'HEADER' or 'PARAGRAPH'.

basically this is what the above trigger does:

for each deleted (or better about to be deleted) row in OMNI_ENT_PAGE_CONTENT the trigger executes following (dynamically created) statement(s), i.e.:

delete from OMNI_ENT_IMAGE where <key_col> = :old.SECTION_ID;

or

delete from OMNI_ENT_LINE where <key_col> = :old.SECTION_ID;

depending on the value :old.type.

the <key_col> should be replaced with the name of the PK from the child tables (ideally it's all the same name)...

I hope this makes things a bit clearer?!
0
 

Author Comment

by:jknj72
ID: 39600152
The child table would all have their own PK names ie(IMAGEID, LINEID, PARAID) so would I need to hard colde the values for each of their names?

Also, if you havent figured this out already, these are elements on a web page. We give the user the ability to create their own page with Headers, Images, Paragraphs, Lines, etc... and we store the values in OMNI_ENT_PAGE_CONTENT. The one I am creating is a template that is created for the user. But...The user has the ability to delete certain items from their page when they do it manually. When my template is created the elements on the page are always the same. I wouldnt want the whole page to be deleted when a user just wants to delete an element out of the table.

If this is the case I may have to loop through the values for a PageID and Delete them manually for my process not to interfere with the way they are currently using the table.

Does this make sense?
0
 
LVL 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 2000 total points
ID: 39600166
The child table would all have their own PK names ie(IMAGEID, LINEID, PARAID) so would I need to hard colde the values for each of their names?
Long story short: yes AND then go for "straight" SQLs, no need for 'execute immediate' like slight already mentioned earlier...
0
 

Author Closing Comment

by:jknj72
ID: 39600195
great job
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39600212
No split of the points?
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39600232
Please reopen the thread and SPLIT the points, that would be more than fair ;-)

Cheers,
Alex
0
 

Author Comment

by:jknj72
ID: 39627552
Is it still possible to split these points? Im sorry I took so long to answer this. Id like to split the points with Alex and Slight. Sorry about the confusion and yes slight you definitely deserve the split.
0
 

Author Comment

by:jknj72
ID: 39630347
Moderator,

4) Points 'Split' between more than one expert comment

by: slightwv  Posted on 2013-10-24 at 12:27:02ID: 39598490

Thanks
JK
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.​
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

604 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