From: Dmitry Tkach <dmitry@openratings.com>
To: Richard Jones <r.d.jones@ed.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: NOT and AND problem
Date: Thu, 17 Jul 2003 10:41:54 -0400
Message-ID: <3F16B5B2.8050602@openratings.com> (raw)
In-Reply-To: <009f01c34c6f$ca98d3c0$5192d781@lib.ed.ac.uk>
References: <BB3C6BB2.20059%awitney@sghms.ac.uk>
<009f01c34c6f$ca98d3c0$5192d781@lib.ed.ac.uk>
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)
>
>
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: dmitry@openratings.com, r.d.jones@ed.ac.uk
Subject: Re: NOT and AND problem
In-Reply-To: <3F16B5B2.8050602@openratings.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