[ILUG-Webdev] MySQL NULL problem

[Niall O Broin]

On Thursday 04 December 2003 12:51, kverens wrote:

> I have a line of SQL which I thought was simple:
> update photographers set state=town where state is null and
> country='Ireland';
>
> this returns 0 changes.
>
> strangely, the below returns 187 results:
> select * from photographers where state is null and country='Ireland';
>
> town is plain text, as is state and country.

What does

select count(* ) from photographers where state is null and town is null and 
country='Ireland';

return  ?

The statement

update photographers set state=town where state is null and country='Ireland';

will return 0 changes if town is already null for all the matching rows.

Of course, I could be missing something glaring. SQL's handling of null has a 
high tripup factor.



Regards,

Niall