Re: delete with self join

[garry saddington <garry(at)schoolteachers(dot)co(dot)uk>]

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