This discussion is archived
6 Replies Latest reply: Dec 5, 2012 2:33 AM by stefan nebesnak RSS

delete row with null value

adf009 Explorer
Currently Being Moderated
hiw how can i delete a row which got a null value in certain column i try this
DELETE FROM ORGANISATIONS WHERE REGISTRATIONNUMBER = null
but i got 0 rows deleted but the reg column got null value

Edited by: adf0994 on 2012/12/05 12:16 PM

Edited by: adf0994 on 2012/12/05 12:16 PM
  • 1. Re: delete row with null value
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    with NULL you never use = NULL but IS NULL.

    i.e.:
    DELETE FROM ORGANISATIONS WHERE REGISTRATIONNUMBER IS null;
    Check here AskTom: Null

    Regards.
    Al

    Edited by: Alberto Faenza on Dec 5, 2012 10:18 AM
    add reference to AskTom
  • 2. Re: delete row with null value
    Hoek Guru
    Currently Being Moderated
    Use IS null:
    SQL> select * from t;
    
          COL1       COL2
    ---------- ----------
             1
    
    SQL> delete from t where col2 = null;
    
    0 rows deleted.
    
    SQL> delete from t where col2 IS null;
    
    1 row deleted.
    
    SQL> 
  • 3. Re: delete row with null value
    Hoek Guru
    Currently Being Moderated
    with NULL you never use = NULL but IS NULL.
    Except when you UPDATE (which can be confusing when learning Oracle):
    SQL> select * from t;
    
          COL1       COL2
    ---------- ----------
             1
    
    SQL> update t set col1 is null;
    update t set col1 is null
                      *
    ERROR at line 1:
    ORA-00927: missing equal sign
    
    
    SQL> update t set col1 = null;
    
    1 row updated.
  • 4. Re: delete row with null value
    adf009 Explorer
    Currently Being Moderated
    with NULL you never use = NULL but IS NULL.
  • 5. Re: delete row with null value
    jeneesh Guru
    Currently Being Moderated
    Hoek wrote:
    with NULL you never use = NULL but IS NULL.
    Except when you UPDATE (which can be confusing when learning Oracle):
    That may be because, it is not comparison.. :)
  • 6. Re: delete row with null value
    stefan nebesnak Journeyer
    Currently Being Moderated
    Null In Oracle is an absence of information. A null can be assigned but it cannot be equated with anything, including itself. NULL values represent missing or unknown data.
    --true
    
    select 'true' from dual where exists (select null from dual);
    select 'true' from dual where null is null;
    select 'true' from dual where 1 is not null;
    --false
    
    select 'true' from dual where null = ANY (null);
    select 'true' from dual where null not in (null);
    select 'true' from dual where null in (null);
    select 'true' from dual where null = null;
    select 'true' from dual where null != null;
    select 'true' from dual where 1 = null;
    select 'true' from dual where 1 != null;
    However, Oracle considers two nulls to be equal when evaluating a DECODE function.



    Nulls

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points