Delete records

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

Hi,
It may be trivial.

What is the right syntax in SQL to delete all records from " mytable" where
myfield is empty ?

What is the differance between null and empty string?

Thanks,
Ron
 
If no value has been entered into a field and there is no default value set,
the value is Null.

You can therefore delete those records with:
DELETE FROM MyTable WHERE MyField Is Null;

A zero-length string (ZLS) is not the same as a Null.
Think of Null as meaning unknown, or not applicable.
For example, you leave the Phone field null if you don't know someone's
phone number.

But what if you know the person has no phone?
That data could be represented as a ZLS.
You can now create a query for all the people who have no phone by entering
the criteria:
""
This does not return the people whose phone number is unknown. To query for
them, the criteria would be:
Is Null

In practice, a user looking at the data cannot see any difference between a
Null and a ZLS. For that reason, it is rarely a good idea to use a ZLS in a
database. In the last 5 years, I can only recall one scenario where I have
implemented a ZLS, because it is just too confusing for users.

Even people who create databases sometimes get confused about how to use
nulls, even though they are absolutely essentially, and incredibly useful.
this might help:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html
 
Hi,


Not all database engine allows a string with no character in it (a ZLS).
Joe Celko pointed out that the standard defines a string as a sequence of
one or more characters (form memory, and I didn't verify that affirmation,
believing Mr. Celko on his words). So, to add to the confusion, some data
source will then, internally, represent a null as a ZLS. A typical relevant
example could be Excel, which, while not being a database, may have supplied
data with a ZLS where it should have been a Null. To cover you against that
kind of problem, you can test:

.... WHERE 0=len(myField & "" )


since then, a NULL concatenated with & to a zero character string will
result into a string with a length of 0, and also would do a ZLS
concatenated to another ZLS.

On the other hand, that comparison won't use any index, and it is really
preferable to make it after all other criteria which can use any index.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top