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

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>

Try this:

Code:

---

DELETE TBL1 WHERE SEQ IN
    (SELECT SEQ FROM TBL1
        WHERE ASCII(INPUT_VALUE) < 48
        OR    ASCII(INPUT_VALUE) > 57)

---

Quote:

---

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';

---

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

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');

---

Hey guys! How about NULL's in f2 ? ! ? !

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

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.

What if (2, NULL) is the only row with f1=2 and f2 not a numeric?

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>

I also got the reply from friend,

create or replace function isnumber (in_value varchar2) return number
as
chk_number number;
begin
chk_number := in_value;
return 1;
exception
when others then
return 0; -- is this is character
end;

delete ...
from ..
where (seq,0) = (select seq,isnumber(in_value) from ..);


Enjoy!!

why do have to create PL/SQl when you can do it in SQL statement ?:confused: :confused: