-
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)
Cheers!
OraKid.
-
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');
Last edited by jmodic; 11-01-2004 at 04:32 AM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|