pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: terry@ashtonwoodshomes.com
To: 'Richard Jones' <r.d.jones@ed.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Re: NOT and AND problem
Date: Thu, 17 Jul 2003 10:52:25 -0400
Message-ID: <010e01c34c73$097ad220$2766f30a@development.greatgulfhomes.com> (raw)
In-Reply-To: <010b01c34c6f$ca5bcac0$2766f30a@development.greatgulfhomes.com>
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
>
view thread (6+ messages) latest in thread
Message-ID: <010e01c34c73$097ad220$2766f30a@development.greatgulfhomes.com> Permalink: ../010e01c34c73$097ad220$2766f30a@development.greatgulfhomes.com/ Also on: postgresql.org/message-id/010e01c34c73$097ad220$2766f30a@development.greatgulfhomes.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: terry@ashtonwoodshomes.com, r.d.jones@ed.ac.uk Subject: Re: NOT and AND problem In-Reply-To: <010e01c34c73$097ad220$2766f30a@development.greatgulfhomes.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
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