locked
SQL Statement not working RRS feed

  • Question

  • HellO!

    I have the following SQL Statement which is not working and I have tried and tried to figure out why.

    DELETE FROM dbo.YARDI_REPORTS$
    WHERE [Current] IN ('8', '9','10', NULL)

    It works for [Current] = 8 , 9 or 10 but not if it is NULL.

    Thanks for your help with this!
    Mike


    Mike Kiser

    Monday, November 19, 2012 10:00 PM

Answers

  • Hi EMKISER

    Not sure on the specifics but with NULL you need to use "IS". Here is my example you can run.

    declare @temp table (num int)
    insert into @temp (num) values (1),(2),(3),(NULL),(4),(5)
    
    select * from @temp where num in (1,3) or num = NULL
    select * from @temp where num in (1,3) or num is NULL


    Pérez

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:10 PM
    Monday, November 19, 2012 10:11 PM
  • Hi,

    Try using IS NULL.

    DELETE FROM dbo.YARDI_REPORTS$
    WHERE [Current] IS NULL


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:10 PM
    Monday, November 19, 2012 10:15 PM
  • Using CASE -

    DECLARE @Table TABLE (Id INT IDENTITY(1,1),SomeColumn VARCHAR(10));
    INSERT INTO @Table
    SELECT '8'  union all
    SELECT '9'   union ALL
    SELECT 'NULL'  union all
    SELECT '10'   union all 
    SELECT '5'   union all  
    SELECT ''   union all   
    SELECT ' '   union all    
    SELECT NULL
       
    SELECT * FROM @Table 
    DELETE FROM @Table 
    WHERE SomeColumn IN('8','9')
    OR 0=(CASE WHEN SomeColumn IS NULL THEN 0 ELSE 1 END)
       
    SELECT * FROM @Table


    Narsimha

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:10 PM
    Monday, November 19, 2012 10:22 PM
  • DELETE FROM dbo.YARDI_REPORTS$
    WHERE [Current] IN ('8', '9','10')
    OR [Current] is NULL

    • Proposed as answer by Naomi N Monday, November 19, 2012 10:53 PM
    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:11 PM
    Monday, November 19, 2012 10:49 PM
  • Read the definition of the IN() predicate. Expand it:

    DELETE FROM Yard_Reports 
    WHERE current_something = '8'
    OR current_something = '9'
    OR current_something = '10'
    OR current_something = NULL;

     Also stop using 1970's Sybase dialect and stick legal characters in table names and learn the reserved words in SQL, like CURRENT, so you will not use them for data element names. 

    What does "current_something = NULL" always evaluate to? The basic law of NULLs? Third day of any SQL class?






    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:12 PM
    Tuesday, November 20, 2012 12:33 AM

All replies

  • Hi EMKISER

    Not sure on the specifics but with NULL you need to use "IS". Here is my example you can run.

    declare @temp table (num int)
    insert into @temp (num) values (1),(2),(3),(NULL),(4),(5)
    
    select * from @temp where num in (1,3) or num = NULL
    select * from @temp where num in (1,3) or num is NULL


    Pérez

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:10 PM
    Monday, November 19, 2012 10:11 PM
  • Hi,

    Try using IS NULL.

    DELETE FROM dbo.YARDI_REPORTS$
    WHERE [Current] IS NULL


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:10 PM
    Monday, November 19, 2012 10:15 PM
  • Using CASE -

    DECLARE @Table TABLE (Id INT IDENTITY(1,1),SomeColumn VARCHAR(10));
    INSERT INTO @Table
    SELECT '8'  union all
    SELECT '9'   union ALL
    SELECT 'NULL'  union all
    SELECT '10'   union all 
    SELECT '5'   union all  
    SELECT ''   union all   
    SELECT ' '   union all    
    SELECT NULL
       
    SELECT * FROM @Table 
    DELETE FROM @Table 
    WHERE SomeColumn IN('8','9')
    OR 0=(CASE WHEN SomeColumn IS NULL THEN 0 ELSE 1 END)
       
    SELECT * FROM @Table


    Narsimha

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:10 PM
    Monday, November 19, 2012 10:22 PM
  • DELETE FROM dbo.YARDI_REPORTS$
    WHERE [Current] IN ('8', '9','10')
    OR [Current] is NULL

    • Proposed as answer by Naomi N Monday, November 19, 2012 10:53 PM
    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:11 PM
    Monday, November 19, 2012 10:49 PM
  • Read the definition of the IN() predicate. Expand it:

    DELETE FROM Yard_Reports 
    WHERE current_something = '8'
    OR current_something = '9'
    OR current_something = '10'
    OR current_something = NULL;

     Also stop using 1970's Sybase dialect and stick legal characters in table names and learn the reserved words in SQL, like CURRENT, so you will not use them for data element names. 

    What does "current_something = NULL" always evaluate to? The basic law of NULLs? Third day of any SQL class?






    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by EMKISER Tuesday, November 20, 2012 4:12 PM
    Tuesday, November 20, 2012 12:33 AM
  • Thanks all! That fixed it. I had originally gotten this from an answer to a post on here when I was inquiring on the best way to do this delete.

    DELETE FROM dbo.YARDI_REPORTS$
    WHERE [Current] IN ('8', '9','10', NULL)

    Thanks!
    Mike


    Mike Kiser

    Tuesday, November 20, 2012 4:12 PM
  • Weird. Can you post a link to that answer? Apparently someone made a mistake.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 20, 2012 4:15 PM
  • Sure I'll try. It has been a couple months....

    Mike Kiser

    Tuesday, November 20, 2012 4:21 PM