Re: delete with self join

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
);


--
   Richard Huxton
   Archonet Ltd