Solved

Can the thing be accomplished in a single Query?

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

706 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

20 Experts available now in Live!

Get 1:1 Help Now