Re: NOT and AND problem - Mailing list 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)
>