6 Replies Latest reply: Dec 5, 2012 4:33 AM by stefan nebesnak RSS

    delete row with null value

    Tshifhiwa
      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
          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
            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
              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
                Tshifhiwa
                with NULL you never use = NULL but IS NULL.
                • 5. Re: delete row with null value
                  jeneesh
                  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
                    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