Re: delete with self join

On Tue, 2007-04-17 at 09:21 +0100, Richard Huxton wrote:
> garry saddington wrote:
> > I am trying this syntax which is my interpretation of the docs:
> >
> > delete from siblings s1 using siblings s2
> > WHERE s1.principal = s2.principal
> > and s1.sibling=175
> >
> > Can anyone tell me where I am going wrong?
>
> 1. What's happening - are you getting an error?
I am getting a syntax error (via psycopg) at or near s1 - perhaps this is a psycopg problem?
> 2. What is the query supposed to do? I can't see why you're not just doing:
> DELETE FROM siblings WHERE sibling=175;
>

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,students.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