delete with self join

--- garry saddington <garry@schoolteachers.co.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