Delete based on boolean record found
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Delete based on boolean record found

  1. #1
    Join Date
    Oct 2000
    Posts
    250

    Delete based on boolean record found

    Dear All,
    Would like to ask anyone write sql command to delete records where particular field has non-numeric value.

    for instance...
    seq input_value
    ---- ----------
    1 1
    1 2
    2 1
    2 2
    2 r
    3 1
    3 2
    3 3

    Expexting output

    seq input value
    ---- -----------
    1 1
    1 2
    3 1
    3 2
    3 3

    You can see once the input_value detect character, it will delete the whole records where seq = 2.

    I know plsql can achieve this, but just want to know whether can achieve in SQL statement.

    Thanks,
    ckwan

  2. #2
    Join Date
    Dec 2000
    Posts
    126
    1* select * from foo
    SQL> /

    F1 F2
    --------- --------------------
    1 1
    1 2
    1 3
    2 1
    2 2
    2 r
    3 1
    3 3
    3 2

    9 rows selected.

    SQL>
    SQL> delete from foo
    2 where f1 in (
    3 select f1 from foo
    4 where not (f2 between '0' and '9')
    5 );

    3 rows deleted.

    SQL> select * from foo;

    F1 F2
    --------- --------------------
    1 1
    1 2
    1 3
    3 1
    3 3
    3 2

    6 rows selected.

    SQL>

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Try this:

    Code:
    DELETE TBL1 WHERE SEQ IN
        (SELECT SEQ FROM TBL1 
            WHERE ASCII(INPUT_VALUE) < 48 
            OR    ASCII(INPUT_VALUE) > 57)
    Cheers!
    OraKid.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by culonbu
    ...
    SQL> delete from foo
    2 where f1 in (
    3 select f1 from foo
    4 where not (f2 between '0' and '9')
    5 );
    ...
    Isn't that the same as ...
    Code:
    Delete From Foo
    Where f2 Not Between '0' And '9';
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2000
    Posts
    126
    posted by slimdave:

    --------------------------------------------------------------------------------

    Delete From Foo
    Where f2 Not Between '0' And '9';
    --------------------------------------------------------------------------------

    This SQL do NOT delete marked with <===

    F1 F2
    --------- --------------------
    1 1
    1 2
    1 3
    2 1 <===
    2 2 <===
    2 r
    3 1
    3 3
    3 2

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Another variation:
    Code:
    delete from foo foo1
    where exists
      (select null from foo foo2
        where foo2.f1 = foo1.f1
          and foo2.f2 not between '0' And '9');
    Last edited by jmodic; 11-01-2004 at 03:32 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hey guys! How about NULL's in f2 ? ! ? !

    (P.S. I don't like ASCII - machine dependant.)

  8. #8
    Join Date
    Dec 2000
    Posts
    126

    Wink

    NO PROBLEM MY FRIEND


    SQL> select * from foo
    2 ;

    F1 F2
    --------- --------------------
    1 1
    1 2
    1 3
    2 2
    2 r
    3 1
    3 3
    3 2
    2

    9 rows selected.

    SQL> select * from foo where f2 is null;

    F1 F2
    --------- --------------------
    2

    SQL> delete from foo
    2 where f1 in (
    3 select f1 from foo
    4 where not (f2 between '0' and '9')
    5 );

    3 rows deleted.

    SQL> select * from foo ;

    F1 F2
    --------- --------------------
    1 1
    1 2
    1 3
    3 1
    3 3
    3 2

    6 rows selected.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    What if (2, NULL) is the only row with f1=2 and f2 not a numeric?

  10. #10
    Join Date
    Dec 2000
    Posts
    126

    Wink

    THERE YOU GO, OLD CRANKY


    SQL> select * from foo where f2 is null;

    F1 F2
    --------- --------------------
    2

    SQL> delete from foo
    2 where f1 in (
    3 select f1 from foo
    4 where not (f2 between '0' and '9') or f2 is null
    5 );

    1 row deleted.

    SQL> select * from foo;

    no rows selected

    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width