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