SQL Queries

A group member in one of my Usability classes asked me an interesting question regarding deleting records from multiple tables in a database.

My answer, well, it depends on the data. But, what does your query look like:

delete from MAIN_TBL where REQUIREMENT in
(select REQUIREMENT
from MAIN_TBL A, LINE_TBL B, DETL_TBL C
where A.REQUIREMENT = B.REQUIREMENT
and A.REQUIREMENT = C.REQUIREMENT
and B.REQUIREMENT = C.REQUIREMENT
and A.eff_status = 'I');

So, my initial reaction was....there aren't any attributes you're deleting. What are you deleting from MAIN_TBL?

So, I said first thing, is the select statement performing the way you want? Is REQUIREMENT really the primary key? How can requirement be unique? If it is, what do you want to delete? Do you want to delete the entire row of data and all the data in the child tables? Finally, is this TEST_DATA? If it is test data I'd probably try and delete the rows from one of the child tables first, then add in both child tables, then add in the parent table. Iteration is our friend.

Start with:

delete * from detl_tbl where requirement in
(select b.requirement from main_tbl a, line_tbl b, detl_tbl c
where a.requirement = b.requirement (+)
and a.requirement = c.requirement (+)
and UPPER(a.eff_status) = 'I');


So, yay. I get to use some of my database troublshooting skills