 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
hilz
Guest
|
 Posted: Wed May 25, 2005 5:45 am Post subject: basic sql question |
 |
|
Hi all
I have a basic sql situation that is bugging me. I could not find the
answer anywhere that i looked, and i could not find a newsgroups
specialized in sql to ask this question.
If this is not the right place to ask, please be gentle! and point me in
the right direction.
Please note that this is a real situation i am running into, but i
simplified it to this example. So this is not a homework (if you were
wondering!)
here we go...
i have four tables T1, T2, T3 and T4 related to each other in a one to
many relation (one in T1 to many in T2. one in T2 to many in T3, and one
in T3 to many in T4).
how would i delete all rows in T4 that are related (or trace back) to a
specific record in T1.
let's assume this structure for simplicity:
TABLE T1
ID (PK)
TABLE T2
ID (PK)
T1_ID (FK)
TABLE T3
ID (PK)
T2_ID (FK)
TABLE T4
ID (PK)
T3_ID (FK)
so now i want to delete all rows in table T4 that trace back to a
specific row in T1.
Thanks for any help.
hilz.
|
|
| Back to top |
|
 |
Arnaud Berger
Guest
|
| Posted: Wed May 25, 2005 6:06 am Post subject: Re: basic sql question |
|
|
Hi,
Not sure this may work, but give it a try :
delete from T4 where id in
( select id from T4 where T3_ID in
((select id from T3 where T2_ID in
(select id from T2 where T1_ID="yourId"))))
Regards,
Arnaud
"hilz" <now@y.com> a écrit dans le message news:
ChUke.225$xu3.64 (AT) lakeread02 (DOT) ..
| Quote: | Hi all
I have a basic sql situation that is bugging me. I could not find the
answer anywhere that i looked, and i could not find a newsgroups
specialized in sql to ask this question.
If this is not the right place to ask, please be gentle! and point me in
the right direction.
Please note that this is a real situation i am running into, but i
simplified it to this example. So this is not a homework (if you were
wondering!)
here we go...
i have four tables T1, T2, T3 and T4 related to each other in a one to
many relation (one in T1 to many in T2. one in T2 to many in T3, and one
in T3 to many in T4).
how would i delete all rows in T4 that are related (or trace back) to a
specific record in T1.
let's assume this structure for simplicity:
TABLE T1
ID (PK)
TABLE T2
ID (PK)
T1_ID (FK)
TABLE T3
ID (PK)
T2_ID (FK)
TABLE T4
ID (PK)
T3_ID (FK)
so now i want to delete all rows in table T4 that trace back to a
specific row in T1.
Thanks for any help.
hilz.
|
|
|
| Back to top |
|
|
Bjorn Abelli
Guest
|
| Posted: Wed May 25, 2005 9:40 am Post subject: Re: basic sql question |
|
|
"Arnaud Berger" wrote...
| Quote: | "hilz" wrote:
i have four tables T1, T2, T3 and T4 related to each other
in a one to many relation (one in T1 to many in T2. one in
T2 to many in T3, and one in T3 to many in T4).
how would i delete all rows in T4 that are related
(or trace back) to a specific record in T1.
Not sure this may work, but give it a try :
delete from T4 where id in
( select id from T4 where T3_ID in
((select id from T3 where T2_ID in
(select id from T2 where T1_ID="yourId"))))
|
Not necessary with that many levels in the subqueries...
delete from T4
where T3_ID in
( select T3.ID
from T3, T2
where T3.T2_ID = T2.ID
and T2.T1_ID = ? )
I've put a question mark where you insert the id for T1 as a parameter in a
PreparedStatement.
// Bjorn A
|
|
| Back to top |
|
|
hilz
Guest
|
| Posted: Wed May 25, 2005 1:25 pm Post subject: Re: basic sql question |
|
|
| Quote: | Not sure this may work, but give it a try :
delete from T4 where id in
( select id from T4 where T3_ID in
((select id from T3 where T2_ID in
(select id from T2 where T1_ID="yourId"))))
Not necessary with that many levels in the subqueries...
delete from T4
where T3_ID in
( select T3.ID
from T3, T2
where T3.T2_ID = T2.ID
and T2.T1_ID = ? )
|
Thanks to you Bjorn and Arnaud. That was pretty quick answer.
I like Bjorn's way since i might have more levels!
thank you very much
hilz
|
|
| Back to top |
|
|
Arnaud Berger
Guest
|
| Posted: Thu May 26, 2005 6:28 am Post subject: Re: basic sql question |
|
|
Quite right, this looks a bit cleaner ;)
Regards,
Arnaud
"Bjorn Abelli" <bjorn_abelli (AT) DoNotSpam (DOT) hotmail.com> a écrit dans le message
news: [email]4294480e_1 (AT) x-privat (DOT) org[/email]...
| Quote: | "Arnaud Berger" wrote...
"hilz" wrote:
i have four tables T1, T2, T3 and T4 related to each other
in a one to many relation (one in T1 to many in T2. one in
T2 to many in T3, and one in T3 to many in T4).
how would i delete all rows in T4 that are related
(or trace back) to a specific record in T1.
Not sure this may work, but give it a try :
delete from T4 where id in
( select id from T4 where T3_ID in
((select id from T3 where T2_ID in
(select id from T2 where T1_ID="yourId"))))
Not necessary with that many levels in the subqueries...
delete from T4
where T3_ID in
( select T3.ID
from T3, T2
where T3.T2_ID = T2.ID
and T2.T1_ID = ? )
I've put a question mark where you insert the id for T1 as a parameter in
a
PreparedStatement.
// Bjorn A
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
