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 [EMAIL PROTECTED] 0131 651 1611
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
