Solved

Can the thing be accomplished in a single Query?

Posted on 2004-10-18
5
264 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backtracking recursion  code 19 54
difference of if loops 23 43
varialbe initialization 11 30
tomcat administrtor 12 34
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now