List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 12 1999 2:09pm
Subject:Re: MySQL and NULL entires
View as plain text  
Mike Machado wrote:
> 
> I did an alter table to add a column called billgroup as an int(10)
> 
> This worked ok and it set all the existing rows to have "NULL" in the
> new column. So I figured I would do a mass update to switch it then to
> "1".  These are the statements I executed but did not seem to change
> anything:
> 
> select billgroup from account limit 10;
> 
> +-----------+
> | billgroup |
> +-----------+
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> +-----------+
> 10 rows in set (0.01 sec)
> 
> 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";
> 
> select billgroup from account limit 10;
> +-----------+
> | billgroup |
> +-----------+
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> |      NULL |
> +-----------+
> 10 rows in set (0.01 sec)
> 
> ?
> 
> What is the where clause I have to use to match the data in the columns
> after an alter table?
> 
> MySQL 3.22.13 - Linux 2.0.36 libc5
> 
> --
> Mike Machado

Hi Mike

Please do never ever compare NULL with = or <> or !=.
Always use xxxx IS NULL or xxxx IS NOT NULL!
So you should use:
UPDATE account SET billgroup = 1 WHERE billgroup IS NULL

Tschau
Christian

Thread
MySQL and NULL entiresMike Machado12 Mar
  • Re: MySQL and NULL entiresSasha Pachev12 Mar
  • Re: MySQL and NULL entiresThimble Smith12 Mar
  • Re: MySQL and NULL entiresChris12 Mar
  • Re: MySQL and NULL entiresChristian Mack12 Mar
  • Re: MySQL and NULL entiresKarl Pielorz12 Mar
  • Re: MySQL and NULL entiresEd Carp12 Mar
  • Searching for CEOs against software patents"Jonas Norrman6 Oct
  • Re: MySQL quota problemArjen Lentz21 Jan
Re: MySQL quota problemHeikki Tuuri21 Jan