Re: delete with self join

On Apr 17, 2007, at 11:07, garry saddington wrote:
>>
>
> I am keeping a record of siblings in a school. The user chooses one
> student and there siblings such that id's are entered into a table as
> such:
> TABLE SIBLINGS:
>
> principal sibling
> 809 234
> 809 785
> 809 345
> 809 809
>
> 809 is a sibling of all of them, but of course 234 is a sibling of
> 785.
> To retrieve siblings I use this query:
>
> SELECT
> students.studentid,students.firstname,students.surname,students.year,s
> tudents.pastoralgroup,students.dob
> FROM siblings c, siblings c2,students
> WHERE c.principal = c2.principal
> and c.sibling=234 (this value is supplied in a variable)
> and c2.sibling=students.studentid
>
> What I am trying to do is to allow the user to correct input
> mistakes by
> deleting all the siblings of one family at the same time by choosing
> just one of the siblings. I hope this clears things up.
> Regards
> Garry
>
What about a
DELETE FROM siblings WHERE principal IN (SELECT principal FROM
siblings WHERE sibling = 42)
?

Thomas