CODEWITHSUNDEEP
sqlnull
Karan1210
Karan1210

Reputation: 63

How to delete a row in SQL based on a NULL condition

I just started learning SQL; I've created a table. Learned insert command and inserted values in 2 rows. However I've inserted null values in 3rd.

Now I want to delete the third row which has 2 columns with no values in it.

I'm using the following query: 

delete employee where city=null;

It doesn't seem to be working!

Upvotes: 6

Views: 13428

Answers (6)

Umer Khan Yousefzai
Umer Khan Yousefzai

Reputation: 21

To achieve this you will have to write this query

DELETE from table_name WHERE city IS NULL;

this query will delete the rows/records WHERE city is null

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10284

Here you know you have added Null in city column so checking with is null will work but it's also possible to add an empty string in city column. So if your business condition is like delete all records from employee table where city is either null or empty i.e. with no values you should write as:

delete from employee where isnull(city,'')='';

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186843

According SQL 92 standard many logical operations with null values like

   > null
   = null
  and null
   or null
  not null

should always return null (and never true). Some DBMS (e.g. Oracle) follow this rule rigorously, some (MS SQL) can have a mode that null = null returns true, not required null. In order to be compartible with SQL 92 and so with (almost) all DBMSs, you should use is null or is not null standard comparisons, in your case

  delete from employee 
        where city is null -- <- Standard comparison

Upvotes: 8

Vaimin
Vaimin

Reputation: 7

change it :

delete from table_name where city is null

or

delete from table_name where city = null

Upvotes: -3

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40613

You need the is null "operator":

delete from employee where city is null;

This is because in SQL, nothing is equal to NULL.

Upvotes: 4

Tobberoth
Tobberoth

Reputation: 9537

You can't use = with NULL. Instead, use:

delete employee where city is null;

Upvotes: 3

Related Questions