Saturday, March 29, 2008

How To Delete a null record

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(NULL)


--Check what's in the table
SELECT * FROM #TestDeleteNull

--This won't work
DELETE #TestDeleteNull
WHERE SomeDate = NULL
--(0 row(s) affected)

--This is the way
DELETE #TestDeleteNull
WHERE SomeDate IS NULL
--(1 row(s) affected)

--Check again
SELECT * FROM #TestDeleteNull

24 comments:

VARTATE said...

great code. thanks.

SUMAN said...

thank a lot..it helped me

Khurram said...

Thanks a lot

Raju said...

Thanks. It helped.

arumugam said...

thankfully gave a tips

rduke131 said...

thanks a lotttttttttttttt.it helped me

ramneet said...

Very Nice, Thanks A Lot...

RJS said...

Great help - thanks for being succinct and clear

RJS said...

Thanks - perfect for what I needed - I apprecaite you keepin it simple and succinct.

Tejaswini said...

Thanks alot yar....

smita said...

thank yaar

OpenSAGA said...

Thank u so so much!!!!

OpenSAGA said...

Thank u so so much!!!

vignesh said...

superb............

vignesh said...

superb............

saravanan ramamoorthy said...
This comment has been removed by the author.
saravanan ramamoorthy said...

Thanks Boss...

Perma Frost said...

thanks alot! saved alot of time!

JideshDavid said...

Thank u so much...

JideshDavid said...
This comment has been removed by the author.
J9139213 said...

Good Stuff - Thanks

praveen kumar said...

thanks sir.....

Mario Berthely said...

thanks for the advice, i was stuck

Mario Berthely said...

thanks i was stuck