Solved

Delete from multiple tables

Posted on 2013-10-24
28
468 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
  • 10
  • 9
  • 8
28 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
slightwv's right: try first to use FKs with casc. constr.
if that could not be applied, then you could try using triggers...
0
 
LVL 76

Expert Comment

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

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
Throw the task back to the designers/developers.
HELL YEAH!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

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

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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
Comment Utility
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
: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
Comment Utility
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 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 500 total points
Comment Utility
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
Comment Utility
great job
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
No split of the points?
0
 
LVL 13

Expert Comment

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

Cheers,
Alex
0
 

Author Comment

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

743 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

16 Experts available now in Live!

Get 1:1 Help Now