?
Solved

Can the thing be accomplished in a single Query?

Posted on 2004-10-18
5
Medium Priority
?
270 Views
Last Modified: 2010-03-31
I have three tables

Table1(key1,key2)
Table2(key3,key1)
Table3(key5,key3)

key1 from Table1 is used as a foreign key in Table2 and key3 from Table2 is used as a foreign key in Table3. I need to delete from T1 all rows where key1 = 0 . For this I ll have to delete the respective foreign key enteries from T2 and T3.  Whether the whole thing could be accomplished in a single query?? or at the most two queries? Helps in this regard appreciated.

Regards
0
Comment
Question by:engineer007
[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
  • 3
5 Comments
 
LVL 37

Accepted Solution

by:
zzynx earned 75 total points
ID: 12336861
An alternative is to define a cascade delete in your database
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12336966
yeah, create foreign keys between the tables (table2(key1) -> table1(key1), and table3(key1) ->table1(key1), with the foreign keys defined with "CASCADE DELETE" set. then deleting from table1 will automatically delete the relevant rows from table2 and table3.
0
 
LVL 21

Expert Comment

by:MogalManic
ID: 12337186
Cascade delete is the best bet.  The only problem I can think of is you won't get a row count of deleted rows in table2 and 3.

If you don't want to do that, write a SQL generator that fills in the blanks:
  delete from {1} where {2}
Where {1} is the tableName
           {2} is the Primary key where clause
and delete from the three tables in the same transaction:

delete from table3
where key3 in (select key3 from table2, table1 where table1.key1=table2.key1 and table1.key1=0)
delete from table2
where key1 in (select key1 from table1 where key=1)
delete from table1
where key=0;
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12355592
Thanks

PS. Only a B-grade? What did you expect to get as answer?
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12355620
Oh I see, you simply always give a B.
Here at EE we're used to get
- an A if the comment contains the/a solution,
- a B if the comment helped you to find the solution and
- a C if the comment helped you in some other way
btw, did you know that giving an A-grade doesn't cost you any more points than giving a C-grade
while we get 4, 3 or 2 times (A, B or C) the points of the question?
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

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