Solved

Can the thing be accomplished in a single Query?

Posted on 2004-10-18
5
268 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 25 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
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 covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

728 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