Cascade Delete Problem 1

[ccampbell]

I have a very complex problem that I need some assistance with for anyone willing to tackle this. Here is the situation.

I have 4 relational tables (T1, T2, T3, T4). T1 is the parent table with the PK being v_seq_num. T3 has a pk of l_seq_num. T2 contains an update_date field that I need to key off of.

Relations:
T1 -> T2 by v_seq_num (0...n)
T3 -> T2 by l_seq_num (0...n)
T4 -> T2 by l_seq_num and v_seq_num

Rules:
- I need to find a T1 record that has multiple records in T2 (based on v_seq_num) and delete the older records in T2, T3, and T4 (based on the update_date in T2).
- The results must be packaged into a stored procedure
- Records in T1 will never be deleted only T2, T3, T4.

How is the best way to go about this problem? Here is an example of what I am dealing with

(This record will never be deleted)
T1.v_seq_num = 123

(delete this record and child records based on date)
T2.v_seq_num = 123
T2.l_seq_num = 999
T2.update_date = 3/10/2005

(delete this record and child records based on date)
T2.v_seq_num = 123
T2.l_seq_num = 888
T2.update_date = 5/10/2005

(This is the record I want to keep based on update_date)
T2.v_seq_num = 123
T2.l_seq_num = 777
T2.update_date = 11/10/2005

(delete this record and child records based on date)
T3.l_seq_num = 999
(delete this record and child records based on date)
T3.l_seq_num = 888
(This is the record I want to keep based on T2 update_date)
T3.l_seq_num = 777

(delete this record and child records based on date)
T4.v_seq_num = 123
T4.l_seq_num = 999

(delete this record and child records based on date)
T4.v_seq_num = 123
T4.l_seq_num = 888

(This is the record I want to keep based on T2 update_date)
T4.v_seq_num = 123
T4.l_seq_num = 777

Any help would be greatly appreciated. This seems like a very complex problem and I am still fairly new to SQL. Thanks in advance.

 

[maswien]

try this,

delete t2 from t2 inner join t1
 on t2.v_seq_num = t1.v_seq_num 
  left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
    on t2.v_seq_num = t0.v_seq_num
 where t2.update_date is null 
 
delete t3 from t3 inner join t2 
 on  t3.l_seq_num = t2.l_seq_num
  inner join t1
   on t2.v_seq_num = t1.v_seq_num 
    left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
     on t2.v_seq_num = t0.v_seq_num
 where t2.update_date is null 
 
 delete t4 from t4 inner join t2 
 on  t4.l_seq_num = t2.l_seq_num and t4.v_seq_num = t2.v_seq_num 
  inner join t1
   on t2.v_seq_num = t1.v_seq_num 
    left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
     on t2.v_seq_num = t0.v_seq_num
 where t2.update_date is null

 

[maswien]

sorry missing a filter for the left join, should be:

delete t2 from t2 inner join t1
 on t2.v_seq_num = t1.v_seq_num 
  left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
    on t2.v_seq_num = t0.v_seq_num and t2.update_date = t0.update_date
 where t2.update_date is null 
 
delete t3 from t3 inner join t2 
 on  t3.l_seq_num = t2.l_seq_num
  inner join t1
   on t2.v_seq_num = t1.v_seq_num 
    left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
     on t2.v_seq_num = t0.v_seq_num and t2.update_date = t0.update_date
 where t2.update_date is null 
 
 delete t4 from t4 inner join t2 
 on  t4.l_seq_num = t2.l_seq_num and t4.v_seq_num = t2.v_seq_num 
  inner join t1
   on t2.v_seq_num = t1.v_seq_num 
    left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
     on t2.v_seq_num = t0.v_seq_num and t2.update_date = t0.update_date
 where t2.update_date is null

 

[ccampbell]

I will give it a try and see what I come up with. Thanks for the reply. Is this the best way to accomplish this?

 

[maswien]

I'm sure somebody else may have better idea on this, BTW the where clause for all the 3 SQLs should be:

  where t0.update_date is null

 

[ccampbell]

These return me a null set when I run them. I modified them slightly to perform a select rather than a delete so I could see the record results. In my test database I have 2 records, one of which I need to clean up because it has an older update date. The first query I am running is as follows

select * from t2 inner join t1
 on t2.v_seq_num = t1.v_seq_num 
  left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
    on t2.v_seq_num = t0.v_seq_num
 where t0.update_date is null

Any ideas?

 

[maswien]

ccampbell, you used the SQL I post first time, it misses one filter for the left join, you should try the last SQL I posted plus the where clause is modified. So the correct SQL should be:

select * from t2 inner join t1
 on t2.v_seq_num = t1.v_seq_num 
  left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
    on t2.v_seq_num = t0.v_seq_num 
      and t2.update_date = t0.update_date /* missed */
 where t0.update_date is null

 

[ccampbell]

Thank you, that is what I was missing.

 

[ccampbell]

Okay, the selects work fine when I run them but the deletes give me a foreign key constraint error on the second SQL. The other problem I have is after I run teh first SQL, the other 2 SQL's don't delete anything because there is nothing to join to. Any ideas?

 

[maswien]

That's very good point that I didn't noticed before !

run the 3 SQLs in reverse order, you should be able to do that

 

[ccampbell]

I get a foreign key constraint error when I run them in reverse order. I get that error on the second SQL

 

[maswien]

I get that error on the second SQL

That means T4 is successfully deleted? If the original SQL order is : 1, 2, 3

Now I want you try: 3, 1, 2

This should work.

 

[ccampbell]

That won't work. I have tried that. SQL 3 works fine, SQL 1 works fine, but then SQL 2 (which contains a join to t1) returns 0 rows affected and does not delete the record needed due to the join it has to t1 who's record was deleted in the previous step (SQL 1). Any other ideas?

 

[maswien]

sorry for my mistake, I borrow your idea that creating 3 temoprary table first and then delete the 3 tables, like following:

select t2.v_seq_num into #del_t2
from t2 inner join t1
 on t2.v_seq_num = t1.v_seq_num 
  left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
    on t2.v_seq_num = t0.v_seq_num and t2.update_date = t0.update_date
 where t2.update_date is null 
 
select t3.l_seq_num into #del_t3 
from t3 inner join t2 
 on  t3.l_seq_num = t2.l_seq_num
  inner join t1
   on t2.v_seq_num = t1.v_seq_num 
    left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
     on t2.v_seq_num = t0.v_seq_num and t2.update_date = t0.update_date
 where t2.update_date is null 
 
select t4.l_seq_num into #del_t4
from t4 inner join t2 
 on  t4.l_seq_num = t2.l_seq_num and t4.v_seq_num = t2.v_seq_num 
  inner join t1
   on t2.v_seq_num = t1.v_seq_num 
    left outer join (select v_seq_num, max(update_date) as update_date from t2 group by v_seq_num ) t0 
     on t2.v_seq_num = t0.v_seq_num and t2.update_date = t0.update_date
 where t2.update_date is null

you then get 3 temp table #del_t2, #del_t3, #del_t4, do following :

delete t3 where v_seq_num 
 in (select v_seq_num from #del_t3 )

...

 

[ccampbell]

Excellent, that works great. I appreciate all of your help on this. It was very helpful for me. One last question, how do I release the #del_t4 ... variable so taht I can run the procedure over and over. Right now, unless I rename the variables, I get a SQL error saying it already exists. "There is already an object named '#del_t3 in the database."

Thanks again.

 

[maswien]

In your Query Analyzer, you can delete it by: drop table #del_t3

If you want wrap all these code in a stored procedure, you don't need to release the temporary table, the temporary table is in a scope of a session, so the temporary table within a stored procedure will be automatically released when the stored procedure stops running. The procedure will look like this:

create procedure del_tab
as
begin

select t2.v_seq_num into #del_t2
from ...
 
select t3.l_seq_num into #del_t3 
from  ...
 
select t4.l_seq_num into #del_t4
from ...

delete t2 where v_seq_num 
 in (select v_seq_num from #del_t2 )

delete t3 where v_seq_num 
 in (select v_seq_num from #del_t3 )

delete t4 where v_seq_num 
 in (select v_seq_num from #del_t4 )

end

 

[ccampbell]

Thank you, that answers it.