SQL Delete values including NULL

Question

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'.

Pரதீப் · Accepted Answer · 2016-12-07 11:47:16Z

10

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)

answered Dec 7, 2016 at 11:47

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

koolkoda · Accepted Answer · 2016-12-07 12:10:41Z

0

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')

answered Dec 7, 2016 at 12:02

koolkoda

3653 silver badges6 bronze badges