Re: 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(at)greatgulfhomes(dot)com
Fax: (416) 441-9085

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Richard Jones
> Sent: Thursday, July 17, 2003 10:29 AM
> To: pgsql-sql(at)postgresql(dot)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(dot)d(dot)jones(at)ed(dot)ac(dot)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(at)postgresql(dot)org)
>