Re: NOT and AND problem - Mailing list pgsql-sql
From | Dmitry Tkach |
---|---|
Subject | Re: NOT and AND problem |
Date | |
Msg-id | 3F16B5B2.8050602@openratings.com Whole thread Raw |
In response to | NOT and AND problem ("Richard Jones" <r.d.jones@ed.ac.uk>) |
List | pgsql-sql |
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) > >