Home » SQL & PL/SQL » SQL & PL/SQL » Delete records from child and parent table [merged 2 by jd] (Oracle 11g)
Delete records from child and parent table [merged 2 by jd] [message #573662] Wed, 02 January 2013 03:17 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I want to delete records from parent table which are less than 2 years.
Before deleting records from parent table we have to delete records from child table
how can we delete those records.
I don't want to use ON DELETE CASCADE.

MASS_MASTER --parent table.
MASS_CHILD --child table.


The below query is used to delete records from parent table.

DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);


The child table MASS_CHILD is not having last_date column.

Please provide me the query to delete same records from child table.

Please help me.

Thanks in advance.
Re: Delete records from child and parent table. [message #573663 is a reply to message #573662] Wed, 02 January 2013 03:20 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If these tables really are master-detail, then there must be a column (or columns) which join these two tables. Suppose it is an ID. Then you'd first delete details:
delete from mass_child
  where id in (select id from mass_master
               where last_date <= add_months(sysdate, -24)
              )
and then delete master table.

Note that you used SYSDATE - it contains time component as well, so - maybe you'd want to use TRUNC(SYSDATE) instead.
Re: Delete records from child and parent table. [message #573665 is a reply to message #573662] Wed, 02 January 2013 03:21 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
Then in my opinion you must delete child records at first by matching with the parent table (which falls under the criteria last_date<=ADD_MONTHS(sysdate,-24)) and then delete from the parent table.
Re: Delete records from child and parent table. [message #573673 is a reply to message #573665] Wed, 02 January 2013 04:13 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.

I have written the query as below and it's working fine.

DELETE FROM mass_child child where exists(select 1 FROM mass_master master
WHERE child.m_id=master.m_id AND last_date<=ADD_MONTHS(sysdate,-24));


However for my another table last_date column is not there in parent table and last_date column is existed in child table.
And the business user requirement is delete records from parent table which are less than 3 months.
It will be like below query.
DELETE FROM GRAG_MASTER WHERE column_name<=ADD_MONTHS(sysdate,-3);

Then how can we delete data from parent table.
DESC GRAG_MASTER
g_id,
gname,
gloc


DESC GRAG_CHILD
g_id,
g_status,
g_code,
last_date


Please help me.
Re: Delete records from child and parent table. [message #573674 is a reply to message #573673] Wed, 02 January 2013 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you'll have to use some workaround because, if you first delete detail records, you don't know which master ones to delete.

For example: delete details first. Then
delete from grag_master m
  where m.g_id not in (select c.g_id from grag_child c);


Or, create a (temporary) table of G_IDs you want to delete:
create table temp_gid as
  select c.g_id
  from grag_child c
  where c.last_date ... <your condition here>;

create index i1_tgid on temp_gid (g_id);

delete from grag_child where ... <your condition here>;

delete from grag_master where g_id in (select g_id from temp_gid);

drop table temp_gid;


Or, use a PL/SQL solution (which might be rather slow, so you'd probably want to avoid it).

[Updated on: Wed, 02 January 2013 04:20]

Report message to a moderator

Re: Delete records from child and parent table. [message #573723 is a reply to message #573674] Wed, 02 January 2013 15:37 Go to previous messageGo to next message
Bill B
Messages: 1131
Registered: December 2004
Senior Member
Just curious, why didn't you want to use cascade delete? I had a system where 30 tables were all tied to a master table and I could remove all history for the user by simply removing one row from the master table.

[Updated on: Wed, 02 January 2013 15:37]

Report message to a moderator

How to delete records from sub child,child and parent tables. [message #574272 is a reply to message #573662] Tue, 08 January 2013 08:20 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

My requirement is I want delete records from parent,child and sub-child tables which are less than 30 days old.

In my case the main parent table(test_table_p5) is having date column(LAST_DT)
and remaining child and sub-child's tables are not having any date column so that we have to drive from main parent table.

The main parent table,child and sub child tables are having common column
based on the common column we have to join to main parent table and get the id's for the
records which are less than 30 days old then perform delete.

I am sending table names and common columns of sub child's,child and parent.

TABLE_NAME COMMON COLUMN
TEST_TABLE_P5	
TEST_TABLE_C5	COL1
TEST_TABLE_C6	COL1
TEST_TABLE_CP4	COL1
TEST_TABLE_CP3	COL2
TEST_TABLE_C7	COL3


I am sending the test tables and data.

create table test_table_p5 (col1 number primary key, col2 NUMBER,col3 NUMBER,col4 varchar2(5),LAST_DT DATE);

create table test_table_c5 (col1 number, col2 NUMBER,col3 varchar2(5), CONSTRAINT fk_P5_col1
 FOREIGN KEY (col1) REFERENCES test_table_p5(col1));
 
create table test_table_c6 (col1 number, col2 NUMBER,col3 varchar2(5), CONSTRAINT fk_P5_col1_2
FOREIGN KEY (col1) REFERENCES test_table_p5(col1));

create table test_table_cp4 (col1 number,col2 NUMBER primary key, col3 varchar2(5),CONSTRAINT fk_P5_cp4_col1
 FOREIGN KEY (col1)
 REFERENCES test_table_p5(col1));

create table test_table_cp3 (col1 number,col2 NUMBER,col3 NUMBER primary key,col4 varchar2(5),CONSTRAINT fk_cp4_cp3_col2
 FOREIGN KEY (col2)
 REFERENCES test_table_cp4(col2));

create table test_table_c7 (col1 number,col2 NUMBER,col3 NUMBER,col4 varchar2(5),CONSTRAINT fk_cp3_c2_col3
 FOREIGN KEY (col3)
 REFERENCES test_table_cp3(col3));

insert into test_table_p5 values (1,10,100,'A','6-JAN-2012');
insert into test_table_p5 values (2,20,200,'AA','4-JAN-2012');
insert into test_table_p5 values (3,30,300,'AAA','15-NOV-2012');
insert into test_table_p5 values (4,40,400,'AAAA','20-NOV-2012');
insert into test_table_p5 values (5,50,500,'AAAAA','25-NOV-2012');

insert into test_table_c5 values (1,10,'B');
insert into test_table_c5 values (2,20,'BB');
insert into test_table_c5 values (3,30,'BBB');
insert into test_table_c5 values (4,40,'BBBB');
insert into test_table_c5 values (5,50,'BBBBB');

insert into test_table_c6 values (1,10,'C');
insert into test_table_c6 values (2,20,'CC');
insert into test_table_c6 values (3,30,'CCC');
insert into test_table_c6 values (4,40,'CCCC');
insert into test_table_c6 values (5,50,'CCCCC');


insert into test_table_cp4 values (1,10,'D');
insert into test_table_cp4 values (2,20,'DD');
insert into test_table_cp4 values (3,30,'DDD');
insert into test_table_cp4 values (4,40,'DDDD');
insert into test_table_cp4 values (5,50,'DDDDD');

insert into test_table_cp3 values (1,10,100,'E');
insert into test_table_cp3 values (2,20,200,'EE');
insert into test_table_cp3 values (3,30,300,'EEE');
insert into test_table_cp3 values (4,40,400, 'EEEE');
insert into test_table_cp3 values (5,50,500,'EEEEE');


insert into test_table_c7 values (1,10,100,'F');
insert into test_table_c7 values (2,20,200,'FF');
insert into test_table_c7 values (3,30,300,'FFF');
insert into test_table_c7 values (4,40,400, 'FFFF');
insert into test_table_c7 values (5,50,500,'FFFFF');


Please help me how to achieve this.

Thanks.

[EDITED by LF: switched title and version information]

[Updated on: Tue, 08 January 2013 12:19] by Moderator

Report message to a moderator

Re: Oracle 11g [message #574274 is a reply to message #574272] Tue, 08 January 2013 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/m/573662/136107/#msg_573662
Re: Oracle 11g [message #574287 is a reply to message #574272] Tue, 08 January 2013 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just drop and recreate the constraints with ON DELETE CASCADE option.

Regards
Michel
Re: Delete records from child and parent table. [message #574295 is a reply to message #573723] Tue, 08 January 2013 12:31 Go to previous messageGo to next message
joy_division
Messages: 4546
Registered: February 2005
Location: East Coast USA
Senior Member
Please stop creating new topics for the same question.

[Updated on: Tue, 08 January 2013 12:32]

Report message to a moderator

Re: Oracle 11g [message #574313 is a reply to message #574287] Tue, 08 January 2013 22:55 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Sorry for creating another post for same requirement.
But I don't want to go for ON DELETE CASCADE.
Please provide me the query how to handle it.
I want to delete the data which is 30 days old.
But except parent table ,child and sub child's not having any date column.
I have to check which records are 30 days old in child and sub child's tables
by using parent table's date column.
Please provide me the query.

Thanks.

Re: Oracle 11g [message #574314 is a reply to message #574313] Tue, 08 January 2013 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
>Please provide me the query.
Nobody here is required to provide anything to you.
What is YOUR contribution to solving YOUR problem?
Re: Oracle 11g [message #574323 is a reply to message #574314] Wed, 09 January 2013 01:02 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
After you tried 3 options suggested here, what was your conclusion? Why none of these did the job? Could you, please, post code you wrote for each of these 3 options so that we could review and, hopefully, fix it?
Re: Oracle 11g [message #574324 is a reply to message #574313] Wed, 09 January 2013 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ut I don't want to go for ON DELETE CASCADE.


We already asked you WHY?
Please reply.
Ignoring our questions is a rude behaviour and will just lead to have NO help from us.

Regards
Michel
Re: Oracle 11g [message #574331 is a reply to message #574324] Wed, 09 January 2013 01:41 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Sorry for rude behavior.
Client is not agree for that.
Please provide the query.

Thanks.
Re: Oracle 11g [message #574335 is a reply to message #574323] Wed, 09 January 2013 02:35 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Littlefoot wrote on Wed, 09 January 2013 08:02
After you tried 3 options suggested here, what was your conclusion? Why none of these did the job? Could you, please, post code you wrote for each of these 3 options so that we could review and, hopefully, fix it?

Re: Oracle 11g [message #574392 is a reply to message #574335] Wed, 09 January 2013 23:01 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have tried the following 6 delete statements for all 6 tables.
How can we achieve this in a single query.

DELETE FROM APPS_GLOBAL.TEST_TABLE_C7 ch where exists
(select 1 from APPS_GLOBAL.TEST_TABLE_CP3 cp3,APPS_GLOBAL.TEST_TABLE_CP4 cp4,APPS_GLOBAL.TEST_TABLE_C5 c5,
APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
WHERE ch.col3=cp3.col3
AND cp3.col2=cp4.col2
AND cp4.col1=p5.col1
AND c5.col1=p5.col1
AND c6.col1=p5.col1
AND  (trunc(sysdate) - p5.LAST_DT) < 30)

DELETE FROM APPS_GLOBAL.TEST_TABLE_Cp3 ch where exists
(select 1 from APPS_GLOBAL.TEST_TABLE_CP4 cp4,APPS_GLOBAL.TEST_TABLE_C5 c5,
APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
WHERE ch.col2=cp4.col2
AND cp4.col1=p5.col1
AND c5.col1=p5.col1
AND c6.col1=p5.col1
AND  (trunc(sysdate) - p5.LAST_DT) < 30)


DELETE FROM APPS_GLOBAL.TEST_TABLE_Cp4 ch where exists
(select 1 from APPS_GLOBAL.TEST_TABLE_C5 c5,
APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
WHERE ch.col1=p5.col1
AND c5.col1=p5.col1
AND c6.col1=p5.col1
AND  (trunc(sysdate) - p5.LAST_DT) < 30)

DELETE FROM APPS_GLOBAL.TEST_TABLE_C5 ch where exists
(select 1 from APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
WHERE  ch.col1=p5.col1
AND c6.col1=p5.col1
AND  (trunc(sysdate) - p5.LAST_DT) < 30)


DELETE FROM APPS_GLOBAL.TEST_TABLE_C6 ch where exists
(select 1 from APPS_GLOBAL.TEST_TABLE_P5 p5
WHERE ch.col1=p5.col1
AND  (trunc(sysdate) - p5.LAST_DT) < 30)

DELETE FROM APPS_GLOBAL.TEST_TABLE_P5 ch where (trunc(sysdate) - ch.LAST_DT) < 30


Please help me.

Thanks.
Re: Oracle 11g [message #574394 is a reply to message #574392] Wed, 09 January 2013 23:09 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
>I have tried the following 6 delete statements for all 6 tables.
>How can we achieve this in a single query.

DELETE can only operate against one table.
Re: Oracle 11g [message #574395 is a reply to message #574394] Wed, 09 January 2013 23:11 Go to previous messageGo to next message
Bill B
Messages: 1131
Registered: December 2004
Senior Member
You can only delete from multiple tables by using cascade delete. Your client is a fool.
Re: Oracle 11g [message #574396 is a reply to message #574392] Wed, 09 January 2013 23:12 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Please help me how can we do it in a procedure by using

start with seq_id
connect by prior

to generate all these delete statements.
Re: Oracle 11g [message #574397 is a reply to message #574395] Wed, 09 January 2013 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
>You can only delete from multiple tables by using cascade delete. Your client is a fool.
+1
Re: Oracle 11g [message #574399 is a reply to message #574397] Thu, 10 January 2013 00:00 Go to previous message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just being curious: what does your client have to do with the way you write programs? Would the client accept you setting rules for his job?
Previous Topic: Extract date from string
Next Topic: Query help
Goto Forum:
  


Current Time: Thu Nov 27 19:32:52 CST 2014

Total time taken to generate the page: 0.07746 seconds