pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: NOT and AND problem
6+ messages / 4 participants
[nested] [flat]
* Re: NOT and AND problem
@ 2003-07-17 14:29 terry@ashtonwoodshomes.com
parent: Richard Jones <r.d.jones@ed.ac.uk>
2 siblings, 1 reply; 6+ messages in thread
From: terry@ashtonwoodshomes.com @ 2003-07-17 14:29 UTC (permalink / raw)
To: 'Richard Jones' <r.d.jones@ed.ac.uk>; pgsql-sql
DELETE FROM myTable
WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id)
AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id);
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Jones
> Sent: Thursday, July 17, 2003 10:29 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] NOT and AND problem
>
>
> Dear All,
>
> I am having some confusion over a query which is supposed to
> achieve the
> following: To remove a record from a table if the one or both of the
> columns containing references to other tables no longer point
> to table rows
> which still exist. There are good reasons why I cannot use
> foreign keys to
> maintain referential integrity, but I will not go into them,
> but they give
> rise to the need to "clean-up" my database table from time to
> time. The
> query that I have had most success with looks like this:
>
> DELETE FROM myTable
> WHERE (NOT myTable.item_id = item.item_id)
> AND (NOT myTable.group_id = ep.group_id);
>
> Which is odd, because logically it shouldn't work. What I
> find with the
> above queries is that as follows:
>
> let myTable.item_id = item.item_id be A
> let myTable.group_id = ep.group_id be B
>
> The derived and actual truth tables for the results of the
> where clause
> follow:
>
> Derived:
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 0
> 0 | 1 | 0
> 0 | 0 | 1
>
> Actual:
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 0
> 0 | 1 | 1
> 0 | 0 | 1
>
> This makes no sense to me, as effectively rows 2 and 3 of the
> Actual results
> truth table are the same (unless there's some subtle
> difference with regards
> to the order of the statements, otherwise just substitute A
> for B and vice
> versa).
>
> The result that I actually want from the operation is this:
>
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 1
> 0 | 1 | 1
> 0 | 0 | 1
>
> which would suggest a query like:
>
> DELETE FROM myTable
> WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id =
> ep.group_id);
>
> which ought to provide the above output. Instead, using this
> query, the
> output I get is as follows:
>
> A | B | Result
> 1 | 1 | 1
> 1 | 0 | 1
> 0 | 1 | 1
> 0 | 0 | 1
>
> I can only conclude that Postgres is doing something with
> regards to the
> other two tables which I am unaware of. Can anyone help me
> understand what
> is going on? Any suggestions gratefully received.
>
> Cheers
>
> Richard
>
>
> Richard Jones
> -----------------------
> Systems Developer
> Theses Alive! - www.thesesalive.ac.uk
> Edinburgh University Library
> r.d.jones@ed.ac.uk
> 0131 651 1611
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* NOT and AND problem
@ 2003-07-17 14:29 Richard Jones <r.d.jones@ed.ac.uk>
0 siblings, 3 replies; 6+ messages in thread
From: Richard Jones @ 2003-07-17 14:29 UTC (permalink / raw)
To: pgsql-sql
Dear All,
I am having some confusion over a query which is supposed to achieve the
following: To remove a record from a table if the one or both of the
columns containing references to other tables no longer point to table rows
which still exist. There are good reasons why I cannot use foreign keys to
maintain referential integrity, but I will not go into them, but they give
rise to the need to "clean-up" my database table from time to time. The
query that I have had most success with looks like this:
DELETE FROM myTable
WHERE (NOT myTable.item_id = item.item_id)
AND (NOT myTable.group_id = ep.group_id);
Which is odd, because logically it shouldn't work. What I find with the
above queries is that as follows:
let myTable.item_id = item.item_id be A
let myTable.group_id = ep.group_id be B
The derived and actual truth tables for the results of the where clause
follow:
Derived:
A | B | Result
1 | 1 | 0
1 | 0 | 0
0 | 1 | 0
0 | 0 | 1
Actual:
A | B | Result
1 | 1 | 0
1 | 0 | 0
0 | 1 | 1
0 | 0 | 1
This makes no sense to me, as effectively rows 2 and 3 of the Actual results
truth table are the same (unless there's some subtle difference with regards
to the order of the statements, otherwise just substitute A for B and vice
versa).
The result that I actually want from the operation is this:
A | B | Result
1 | 1 | 0
1 | 0 | 1
0 | 1 | 1
0 | 0 | 1
which would suggest a query like:
DELETE FROM myTable
WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id =
ep.group_id);
which ought to provide the above output. Instead, using this query, the
output I get is as follows:
A | B | Result
1 | 1 | 1
1 | 0 | 1
0 | 1 | 1
0 | 0 | 1
I can only conclude that Postgres is doing something with regards to the
other two tables which I am unaware of. Can anyone help me understand what
is going on? Any suggestions gratefully received.
Cheers
Richard
Richard Jones
-----------------------
Systems Developer
Theses Alive! - www.thesesalive.ac.uk
Edinburgh University Library
r.d.jones@ed.ac.uk
0131 651 1611
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: NOT and AND problem
@ 2003-07-17 14:34 Viorel Dragomir <bc@vio.ro>
parent: Richard Jones <r.d.jones@ed.ac.uk>
2 siblings, 1 reply; 6+ messages in thread
From: Viorel Dragomir @ 2003-07-17 14:34 UTC (permalink / raw)
To: Richard Jones <r.d.jones@ed.ac.uk>; pgsql-sql
----- Original Message -----
From: "Richard Jones" <r.d.jones@ed.ac.uk>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, July 17, 2003 5:29 PM
Subject: [SQL] NOT and AND problem
> Dear All,
>
> I am having some confusion over a query which is supposed to achieve the
> following: To remove a record from a table if the one or both of the
> columns containing references to other tables no longer point to table
rows
> which still exist. There are good reasons why I cannot use foreign keys
to
> maintain referential integrity, but I will not go into them, but they give
> rise to the need to "clean-up" my database table from time to time. The
> query that I have had most success with looks like this:
>
> DELETE FROM myTable
> WHERE (NOT myTable.item_id = item.item_id)
> AND (NOT myTable.group_id = ep.group_id);
>
> Which is odd, because logically it shouldn't work. What I find with the
> above queries is that as follows:
>
> let myTable.item_id = item.item_id be A
> let myTable.group_id = ep.group_id be B
>
> The derived and actual truth tables for the results of the where clause
> follow:
>
> Derived:
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 0
> 0 | 1 | 0
> 0 | 0 | 1
>
> Actual:
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 0
> 0 | 1 | 1
> 0 | 0 | 1
>
> This makes no sense to me, as effectively rows 2 and 3 of the Actual
results
> truth table are the same (unless there's some subtle difference with
regards
> to the order of the statements, otherwise just substitute A for B and vice
> versa).
>
> The result that I actually want from the operation is this:
>
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 1
> 0 | 1 | 1
> 0 | 0 | 1
>
> which would suggest a query like:
>
> DELETE FROM myTable
> WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id =
> ep.group_id);
If u want this u can obtain by
DELETE FROM myTable
WHERE (NOT(myTable.item_id = item.item_id )) OR (NOT(myTable.group_id =
ep.group_id));
You can write ! instead of NOT.
Look at the operations precedence. The NOT might get executed before "=".
>
> which ought to provide the above output. Instead, using this query, the
> output I get is as follows:
>
> A | B | Result
> 1 | 1 | 1
> 1 | 0 | 1
> 0 | 1 | 1
> 0 | 0 | 1
>
> I can only conclude that Postgres is doing something with regards to the
> other two tables which I am unaware of. Can anyone help me understand
what
> is going on? Any suggestions gratefully received.
>
> Cheers
>
> Richard
>
>
> Richard Jones
> -----------------------
> Systems Developer
> Theses Alive! - www.thesesalive.ac.uk
> Edinburgh University Library
> r.d.jones@ed.ac.uk
> 0131 651 1611
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: NOT and AND problem
@ 2003-07-17 14:41 Dmitry Tkach <dmitry@openratings.com>
parent: Richard Jones <r.d.jones@ed.ac.uk>
2 siblings, 0 replies; 6+ messages in thread
From: Dmitry Tkach @ 2003-07-17 14:41 UTC (permalink / raw)
To: Richard Jones <r.d.jones@ed.ac.uk>; +Cc: pgsql-sql
I can't help you explain what is going on with this query - like you, I
am puzzled by the fact that it actually works, and have no idea how it
is interpreted, and what it is doing...
The right way to do what you want, I think, would be something like:
delete from mytable where not exists (select 1 from item where item_id =
mytable.item_id) or not exists (select 1 from ep where
group_id=mytable.group_id);
I replaced your AND with OR, because that's what you seem to be saying
in the description of your problem...
I hope, it helps..
Dima
Richard Jones wrote:
>Dear All,
>
>I am having some confusion over a query which is supposed to achieve the
>following: To remove a record from a table if the one or both of the
>columns containing references to other tables no longer point to table rows
>which still exist. There are good reasons why I cannot use foreign keys to
>maintain referential integrity, but I will not go into them, but they give
>rise to the need to "clean-up" my database table from time to time. The
>query that I have had most success with looks like this:
>
>DELETE FROM myTable
>WHERE (NOT myTable.item_id = item.item_id)
>AND (NOT myTable.group_id = ep.group_id);
>
>Which is odd, because logically it shouldn't work. What I find with the
>above queries is that as follows:
>
>let myTable.item_id = item.item_id be A
>let myTable.group_id = ep.group_id be B
>
>The derived and actual truth tables for the results of the where clause
>follow:
>
>Derived:
>A | B | Result
>1 | 1 | 0
>1 | 0 | 0
>0 | 1 | 0
>0 | 0 | 1
>
>Actual:
>A | B | Result
>1 | 1 | 0
>1 | 0 | 0
>0 | 1 | 1
>0 | 0 | 1
>
>This makes no sense to me, as effectively rows 2 and 3 of the Actual results
>truth table are the same (unless there's some subtle difference with regards
>to the order of the statements, otherwise just substitute A for B and vice
>versa).
>
>The result that I actually want from the operation is this:
>
>A | B | Result
>1 | 1 | 0
>1 | 0 | 1
>0 | 1 | 1
>0 | 0 | 1
>
>which would suggest a query like:
>
>DELETE FROM myTable
>WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id =
>ep.group_id);
>
>which ought to provide the above output. Instead, using this query, the
>output I get is as follows:
>
>A | B | Result
>1 | 1 | 1
>1 | 0 | 1
>0 | 1 | 1
>0 | 0 | 1
>
>I can only conclude that Postgres is doing something with regards to the
>other two tables which I am unaware of. Can anyone help me understand what
>is going on? Any suggestions gratefully received.
>
>Cheers
>
>Richard
>
>
>Richard Jones
>-----------------------
>Systems Developer
>Theses Alive! - www.thesesalive.ac.uk
>Edinburgh University Library
>r.d.jones@ed.ac.uk
>0131 651 1611
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: NOT and AND problem
@ 2003-07-17 14:52 terry@ashtonwoodshomes.com
parent: terry@ashtonwoodshomes.com
0 siblings, 0 replies; 6+ messages in thread
From: terry@ashtonwoodshomes.com @ 2003-07-17 14:52 UTC (permalink / raw)
To: 'Richard Jones' <r.d.jones@ed.ac.uk>; pgsql-sql
And after reading Viorel's response I realized that you wanted the record
deleted if EITHER "foreign key" was broken, not just if BOTH fkeys are
broken, therefore simply change the AND to an OR:
DELETE FROM myTable
WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id)
OR NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id);
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of
> terry@ashtonwoodshomes.com
> Sent: Thursday, July 17, 2003 10:29 AM
> To: 'Richard Jones'; pgsql-sql@postgresql.org
> Subject: Re: [SQL] NOT and AND problem
>
>
> DELETE FROM myTable
> WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id)
> AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id);
>
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
> Fax: (416) 441-9085
>
>
> > -----Original Message-----
> > From: pgsql-sql-owner@postgresql.org
> > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Jones
> > Sent: Thursday, July 17, 2003 10:29 AM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] NOT and AND problem
> >
> >
> > Dear All,
> >
> > I am having some confusion over a query which is supposed to
> > achieve the
> > following: To remove a record from a table if the one or
> both of the
> > columns containing references to other tables no longer point
> > to table rows
> > which still exist. There are good reasons why I cannot use
> > foreign keys to
> > maintain referential integrity, but I will not go into them,
> > but they give
> > rise to the need to "clean-up" my database table from time to
> > time. The
> > query that I have had most success with looks like this:
> >
> > DELETE FROM myTable
> > WHERE (NOT myTable.item_id = item.item_id)
> > AND (NOT myTable.group_id = ep.group_id);
> >
> > Which is odd, because logically it shouldn't work. What I
> > find with the
> > above queries is that as follows:
> >
> > let myTable.item_id = item.item_id be A
> > let myTable.group_id = ep.group_id be B
> >
> > The derived and actual truth tables for the results of the
> > where clause
> > follow:
> >
> > Derived:
> > A | B | Result
> > 1 | 1 | 0
> > 1 | 0 | 0
> > 0 | 1 | 0
> > 0 | 0 | 1
> >
> > Actual:
> > A | B | Result
> > 1 | 1 | 0
> > 1 | 0 | 0
> > 0 | 1 | 1
> > 0 | 0 | 1
> >
> > This makes no sense to me, as effectively rows 2 and 3 of the
> > Actual results
> > truth table are the same (unless there's some subtle
> > difference with regards
> > to the order of the statements, otherwise just substitute A
> > for B and vice
> > versa).
> >
> > The result that I actually want from the operation is this:
> >
> > A | B | Result
> > 1 | 1 | 0
> > 1 | 0 | 1
> > 0 | 1 | 1
> > 0 | 0 | 1
> >
> > which would suggest a query like:
> >
> > DELETE FROM myTable
> > WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id =
> > ep.group_id);
> >
> > which ought to provide the above output. Instead, using this
> > query, the
> > output I get is as follows:
> >
> > A | B | Result
> > 1 | 1 | 1
> > 1 | 0 | 1
> > 0 | 1 | 1
> > 0 | 0 | 1
> >
> > I can only conclude that Postgres is doing something with
> > regards to the
> > other two tables which I am unaware of. Can anyone help me
> > understand what
> > is going on? Any suggestions gratefully received.
> >
> > Cheers
> >
> > Richard
> >
> >
> > Richard Jones
> > -----------------------
> > Systems Developer
> > Theses Alive! - www.thesesalive.ac.uk
> > Edinburgh University Library
> > r.d.jones@ed.ac.uk
> > 0131 651 1611
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: NOT and AND problem
@ 2003-07-17 15:09 Richard Jones <r.d.jones@ed.ac.uk>
parent: Viorel Dragomir <bc@vio.ro>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Jones @ 2003-07-17 15:09 UTC (permalink / raw)
To: Viorel Dragomir <bc@vio.ro>; pgsql-sql; terry@ashtonwoodshomes.com; dmitry@openratings.com
Hello,
Just to say thanks for such fast responses. The full working query is
indeed as suggested (I cut the demo query down when I posted it, as it's got
some awfully long names in it in real life):
DELETE FROM eulepersongroup2workspaceitem
WHERE NOT EXISTS (
SELECT 1 FROM workspaceitem WHERE workspace_item_id =
eulepersongroup2workspaceitem.workspace_item_id
) OR NOT EXISTS (
SELECT 1 FROM epersongroup WHERE eperson_group_id =
eulepersongroup2workspaceitem.eperson_group_id
);
Thanks very much for your help.
Regards
Richard
Richard Jones
-----------------------
Systems Developer
Theses Alive! - www.thesesalive.ac.uk
Edinburgh University Library
r.d.jones@ed.ac.uk
0131 651 1611
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-07-17 15:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-17 14:29 NOT and AND problem Richard Jones <r.d.jones@ed.ac.uk>
2003-07-17 14:29 ` terry@ashtonwoodshomes.com
2003-07-17 14:52 ` terry@ashtonwoodshomes.com
2003-07-17 14:34 ` Viorel Dragomir <bc@vio.ro>
2003-07-17 15:09 ` Richard Jones <r.d.jones@ed.ac.uk>
2003-07-17 14:41 ` Dmitry Tkach <dmitry@openratings.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox