• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Can the thing be accomplished in a single Query?

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
engineer007
Asked:
engineer007
  • 3
1 Solution
 
zzynxSoftware engineerCommented:
An alternative is to define a cascade delete in your database
0
 
cjjcliffordCommented:
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
 
MogalManicCommented:
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
 
zzynxSoftware engineerCommented:
Thanks

PS. Only a B-grade? What did you expect to get as answer?
0
 
zzynxSoftware engineerCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now