Re: delete with self join

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

On Tue, 2007-04-17 at 10:15 +0100, Richard Huxton wrote:
> garry saddington wrote:
> > 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
> 
> > 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.
> 
> Ah, OK. The error then is that you're testing against s1.sibling not 
> s2.sibling. "delete from siblings s1 ... and s1.sibling=175" which means 
> you're not using s2 at all.
> 
> You might find it clearer with a subquery:
> DELETE FROM siblings WHERE principal = (
> 	SELECT principal FROM siblings WHERE sibling=234
> );
Thanks, can't think why I went the complicated route!
Regards
Garry