Re: delete with self join

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?
regards
Garry

--- garry saddington <garry(at)schoolteachers(dot)co(dot)uk> 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?

What is your query do that is different that what you expect? However, I expect that your query
can effectively be reduced to:

delete from siblings where s1.sibling=175;

since "s1.principal = s1.principal" isn't really doing much reduce the number of rows for
deletion.

Regards,
Richard Broersma

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?

2. What is the query supposed to do? I can't see why you're not just doing:
DELETE FROM siblings WHERE sibling=175;

--
Richard Huxton
Archonet Ltd

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

On Apr 17, 2007, at 11:07, garry saddington wrote:
>>
>
> 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,s
> tudents.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
>
What about a
DELETE FROM siblings WHERE principal IN (SELECT principal FROM
siblings WHERE sibling = 42)
?

Thomas

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

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