From: Thimble Smith
Date: March 12 1999 3:39pm
Subject: Re: MySQL and NULL entires
List-Archive: http://lists.mysql.com/mysql/91
Message-Id: <19990312083907.B28088@desert.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

On Thu, Mar 11, 1999 at 11:00:22PM -0800, Mike Machado wrote:
> update account set billgroup = "1" where billgroup = "NULL";
> update account set billgroup = "1" where billgroup = NULL;
> update account set billgroup = "1" where billgroup = "";
> update account set billgroup = "1" where billgroup = '';
> update account set billgroup = "1" where billgroup = "0";

You should read a good book on SQL, and also read the manual.
The short answer to your question is:

    UPDATE account SET billgroup = 1 WHERE billgroup IS NULL;

The long answer is much longer, and if you have any columns
declared without NOT NULL, then you need to know about it.
SQL logic isn't boolean - it's tri-valued, using true, false,
and NULL.  If you want to operate on NULL values, you have to
explicitly say so.

If you don't NEED to allow NULL values (which it sounds like
you don't, in your app), then you really should explicitly
disallow them when you create your tables to prevent this
kind of problem.  In the mean time, read up on just what NULL
means (it is tricky - if you haven't read a good book on it,
then you probably are missing some crucial information).  Then
when you really do need to use NULL, you'll be prepared for it.

Also, it's probably not a good idea to assign a string to a
numeric column if you can help it.  If you declared billgroup
as an integer column, then assign an integer to it (as I did
in my example).  "1" is a string constant.  1 is an integer
constant.  Read the manual on how to express constant values
for more information (it's important).  Also be careful, when
using values in your real programs, that you always quote them
when they may contain special characters.  If you're inserting
information that you've gotten from a user (or any source that
is out of your control), either validate the data so that you
are sure it contains no special characters or always pass it
through a function that escapes special characters (like the
mysql_escape_string() function in C, or $dbh->quote() in Perl).
If you do this, it'll save you a lot of trouble in the long
run!

I hope this helps, and doesn't sound too pedantic.  I'm just
concerned that, if you don't get a basic familiarity with how
SQL works, you're going to wind up writing programs that will
work about 90% of the time - the worst kind of problem to fix!

Tim