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