> >> I always know that you can't compare NULL to anything but I must
> >> say it's just a little ambiguous to have say
> >> update table set field=NULL
> >> instead of
> >> update table set field to null
> >> As infrequently as I use this statement, I find myself writing the
> >> latter and having to correct myself to the former.
>
> Martijn:

(me, disagreeing ;-)

> > Why? The "=" operator in an UPDATE statement
> > doesn't mean "equals", it means "becomes".
> >
> > Hence, FIELD <becomes operator> 5 or
> > FIELD <becomes operator> NULL
>
> The difference being that first you assign a value, and then a state.
>
> Now on the one hand, the SQL standard stresses that "state" and
> "value" are such different concepts that we really, really mustn't use
> the same equality operator for them (hence '=' for value comparison
> and 'is' for state comparison)...
>
> ...but when we're talking assignment, all of a sudden it's no problem
> to use the same operator to assign a value and a state. That's
> inconsistent.

Nope, it isn't...

FIELD becomes 5

FIELD becomes NULL

Sounds fine to me for an assignment.

However,

FIELD equals 5 (hence, NOT NULL)

FIELD equals NULL/unknown ??

> By the way, this has nothing to do with the fact that in SQL the
> equality operator happens to be the same as the assignment operator.
>
> Another inconsistency is found in statements like:
>
> insert into Dogtable ( Name, IQ, Sound )
> values ( 'Pluto', NULL, 'Woof' );
>
> Er? NULL a *value* ?
>
> But, as I said, we'll have to live with it...

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com