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:29:10 -0400
Message-ID: <010b01c34c6f$ca5bcac0$2766f30a@development.greatgulfhomes.com> (raw)
In-Reply-To: <009f01c34c6f$ca98d3c0$5192d781@lib.ed.ac.uk>
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)
>
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: <010b01c34c6f$ca5bcac0$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