Re: Brain Freeze - Request Delete Syntax Help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 29 Feb 2008 07:35:30 -0800 (PST)
Message-ID: <275fa0a6-4e7f-4955-b2a6-2ee8a862bb7c@d4g2000prg.googlegroups.com>


On Feb 29, 10:08 am, spamb..._at_milmac.com (Doug Miller) wrote:
> In article <OpUxj.22325$y05.12..._at_newsfe22.lga>, "Buck Turgidson" <jc..._at_hotmail.com> wrote:
> >I need to reverse the logic of a SELECT statement, and change it into a
> >DELETE statement.
>
> >Do I have this right?  Does this:
> >--------------
> >select * from
> >mytable.
> >where ( aggr = '1' OR ( aggr = '3' AND agg_d <> 'X' ) ).
> >--------------
> >Convert to this for a table that is loaded without the above where clause?
> >--------------
> >delete from mytable
> >  where  ( aggr <> '1' and ( aggr <> '3' AND agg_d = 'X' ) ).
> >--------------
>
> No; the second AND should be OR instead:
>
> You want to negate an expression of the form (A OR B).
> NOT (A OR B) = (NOT A) AND (NOT B)
> But in this case, B is the compound condition (C AND D)
> Thus (NOT B) = (NOT (C AND D)) = (NOT C) OR (NOT D)
> And the final condition is (NOT A) AND ((NOT C) OR (NOT D)).

Doug's explanation is very good. Just keep in mind that the situation changes a bit if NULLs are permitted in the columns. A test setup for the OP to experiment with:
CREATE TABLE T1(
  AGGR VARCHAR2(2),
  ADD_G VARCHAR2(2));

INSERT INTO T1 VALUES ('1',NULL);
INSERT INTO T1 VALUES ('1','X');
INSERT INTO T1 VALUES ('1','Y');
INSERT INTO T1 VAULES ('2',NULL);
INSERT INTO T1 VALUES ('2','X');
INSERT INTO T1 VALUES ('2','Y');
INSERT INTO T1 VALUES ('3',NULL);
INSERT INTO T1 VALUES ('3','X');
INSERT INTO T1 VALUES ('3','Y');
INSERT INTO T1 VALUES (NULL,NULL);

Similar to the original SQL statement:
SELECT
  *
FROM
  T1
WHERE
  AGGR='1'
  OR (AGGR='3' AND ADD_G<>'X');

AG AD
-- --
1 X
1 Y
3 Y

The rows that need to be deleted from the test table: SELECT
  *
FROM
  T1
MINUS
SELECT
  *
FROM
  T1
WHERE
  AGGR='1'
  OR (AGGR='3' AND ADD_G<>'X');

AG AD
-- --
2 X
2 Y
3 X
3 (NULL)
(NULL) (NULL) Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Feb 29 2008 - 09:35:30 CST

Original text of this message