SQL Statement not working

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

Answer 1

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

Answer 2

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!

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

Sure I'll try. It has been a couple months....

---

Mike Kiser