#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    55
    Rep Power
    9

    How to Delete Null Values


    Hi ,
    I have One table Inside the Table, One Column have null values .
    how to delete the null values from the table .Please Help me.

    Thanks.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    10
    Rep Power
    0

    Dleting values from tables


    Its Very Simple Just goto Query Analyzer select Serve->Select Database ->Select Table. and Write a single line in Editor

    DELETE FROM Table name

    Comments on this post

    • pabloj disagrees : This will empty the table
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    You'll probably want to delete from table where column (values) is null.
    Or do you want to update nulls to new values?
    Or do you really want to empty the table?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2006
    Posts
    1
    Rep Power
    0
    Re-opening an old thread ... I had a similar question..

    Originally Posted by pabloj
    You'll probably want to delete from table where column (values) is null.
    Or do you want to update nulls to new values?
    Or do you really want to empty the table?
    I'd like to do the first option - delete rows that contain null values in a given colum. This:
    Code:
    delete from MetaDataInfo where nodePath = NULL;
    doesn't find them (ie: deletes 0 rows..)
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by drevicko
    I'd like to do the first option - delete rows that contain null values in a given colum. This:
    Code:
    delete from MetaDataInfo where nodePath = NULL;
    doesn't find them (ie: deletes 0 rows..)
    There are two possible answers:

    1) you don't have any rows with a null value in the column nodePath;

    2) In SQL Server a null value and an empty value are two different things (at least for character data). So you probably want
    Code:
    delete from MetaDataInfo where nodePath = NULL OR nodePath = '';
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    shammat, FYI, in every other database besides oracle, NULL and empty strings are different

    the correct syntax is
    Code:
    WHERE nodepath IS NULL
    if you want to delete rows which have NULLs and also rows which have empty strings, use
    Code:
    WHERE COALESCE(nodepath,'') = ''
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by r937
    shammat, FYI, in every other database besides oracle, NULL and empty strings are different
    Yes I know
    Side node: none of the other datatypes has the concept of an "empty value", so why do people feel character data needs it?

    It sounded like drevicko assumed that an empty string and NULL is the same thing, so that's why I mentioned it.

    the correct syntax is WHERE nodepath IS NULL
    Good catch, I simply copied the original statement.
    Most probably the real reason why the initial statement did work...

IMN logo majestic logo threadwatch logo seochat tools logo