CODEWITHSUNDEEP
sqlsql-server
Anup
Anup

Reputation: 9738

SQL Delete values including NULL

I want to delete all the records which don't have Remarks as 'Invalid Process' & other 2 conditions as below code :-

 DELETE FROM Entry WHERE EmployeeId = 474 AND Entry_Date = '2016-10-01' 
 AND Remarks <> 'Invalid Process'

But problem here is it doesn't delete the records which have NULL as value. I want to delete all except Remarks as 'Invalid Process'.

Upvotes: 4

Views: 87

Answers (2)

koolkoda
koolkoda

Reputation: 365

You cannot use the operator '<>' when considering NULL.This behavior is defined in the ANSI SQL-92 standard.

use the standard syntax for checking NULLs - "IS NULL"

DELETE FROM Entry WHERE EmployeeId = 474 AND Entry_Date = '2016-10-01' AND ( Remarks IS NULL OR Remarks <> 'Invalid Process')

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Add IS NULL condition. <> operator cannot check NULL values 

 DELETE FROM Entry WHERE EmployeeId = 474 AND Entry_Date = '2016-10-01' 
 AND (Remarks <> 'Invalid Process' or Remarks IS NULL)

Upvotes: 10

Related Questions