join

[jaanutn]

code]

SQL> delete from borrower where name IS null;

0 rows deleted.

SQL> select count(name) from borrower;

COUNT(NAME)
-----------
4

[code]


it is not working

[amitpatel66]

code]

SQL> delete from borrower where name IS null;

0 rows deleted.

SQL> select count(name) from borrower;

COUNT(NAME)
-----------
4

[code]


it is not working

Then you DONOT have any record with name value as NULL. Check your data properly

[jaanutn]

Then you DONOT have any record with name value as NULL. Check your data properly

[code]

SQL> select * from borrower;

NAME LOANNO
-------------------- ---------
jane 43
jeen 89
0


SQL> select count(name) from borrower;

COUNT(NAME)
-----------
4

[code]

an empty record is exist in the database.. wanted to know how to delete an empty record

[amitpatel66]

[code]

SQL> select * from borrower;

NAME LOANNO
-------------------- ---------
jane 43
jeen 89
0


SQL> select count(name) from borrower;

COUNT(NAME)
-----------
4

 
an empty record is exist in the database.. wanted to know how to delete an empty record

 
That can be a space as well.
 
Run this query and post your results here:
 
[code=oracle]
 
SELECT LENGTH(name),name from borrower

[jaanutn]

That can be a space as well.

Run this query and post your results here:

[code=oracle]

SELECT LENGTH(name),na me from borrower

[/code]

[code]
SQL> select length(name), name from borrower;

LENGTH(NAME) NAME
------------ --------------------
4 jane
4 jeen
8
8
[code]

[amitpatel66]

[code]
SQL> select length(name), name from borrower;

LENGTH(NAME) NAME
------------ --------------------
4 jane
4 jeen
8
8
[code]

Can you understand this. For the last two values, it says length as 8 but the name column does not display anything, so it has white spaces and white spaces is NOT EQUAL to NULL, so the query did not delete any record from the table.

[jaanutn]

Can you understand this. For the last two values, it says length as 8 but the name column does not display anything, so it has white spaces and white spaces is NOT EQUAL to NULL, so the query did not delete any record from the table.

[quote]
thank you for the reply....


is there any posibility to delete those records?

[quote]

[amitpatel66]

Check This:

[code=oracle]

SQL> select * from emp
2 /
no rows selected

SQL> desc emp;
Name Null? Type
----------------------------------- ------ ----------------------
ENAME VARCHAR2(20)
EMPNO NOT NULL NUMBER

SQL> insert into emp values(' ',1)
2 /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

ENAME EMPNO
-------------------- ----------
1

SQL> delete from emp where TRIM(ename) IS NULL;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

no rows selected

SQL>

[/code]

I have used TRIM FUNCTION to trim off the white spaces and so any record having only white spaces will become NULL and then delete those using IS NULL condition.

[jaanutn]

Check This:

[code=oracle]

SQL> select * from emp
2 /
no rows selected

SQL> desc emp;
Name Null? Type
----------------------------------- ------ ----------------------
ENAME VARCHAR2(20)
EMPNO NOT NULL NUMBER

SQL> insert into emp values(' ',1)
2 /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

ENAME EMPNO
-------------------- ----------
1

SQL> delete from emp where TRIM(ename) IS NULL;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

no rows selected

SQL>

[/code]

I have used TRIM FUNCTION to trim off the white spaces and so any record having only white spaces will become NULL and then delete those using IS NULL condition.

[quote]

thank you

[quote]