delete from table rows where column is empty

ketanco

Hello,
I am trying to delete rows from a table where one of the columns contain no data. I tried this:

delete from tablename where response column is null;

or tried this too:

delete from tablename where tablename.column is null;

didnt give error but when I checked my table I saw that it didnt delete those rows that has empty fields under that column.

WHen I checked my table structure, I saw that for that column I had put varchar(7) for data type and no to null.

Is it not working because I marked that column as no to null when I was creating the table? If so, what are the empty cells in that column considered? And how can I delete the rows that contain those empty cells?

Weedpacket

You're right; if the column is NOT NULL, then your delete commands won't find any rows to delete because none of them will have NULL in the column. What it will have would be the default value specified for the column.

If a column is marked NOT NULL and no default value was specified, then any insert into the table must have a value for the column or the DBMS would reject the attempt. If the column is not marked NOT NULL, then the DBMS would reject any attempt to change that if there are any rows in the table that had NULLs in that column.

So, either way, if the column is NOT NULL then there aren't any NULLs in the column. What is specified for the column's default value? My wild guess (not being the person who designed the table in the first place) would be an empty string.

djjjozsi

In that case you could select them like this,

SELECT *
FROM table
WHERE field = '' ;

varchar is a string, you must handle that in the sql query too.

ketanco;10890181 wrote:
Hello,
I am trying to delete rows from a table where one of the columns contain no data. I tried this:

delete from tablename where response column is null;

or tried this too:

delete from tablename where tablename.column is null;

didnt give error but when I checked my table I saw that it didnt delete those rows that has empty fields under that column.

WHen I checked my table structure, I saw that for that column I had put varchar(7) for data type and no to null.

Is it not working because I marked that column as no to null when I was creating the table? If so, what are the empty cells in that column considered? And how can I delete the rows that contain those empty cells?