From: "Clay Shannon" <cshannon@...>

> Why does this SQL:
>
>
>
> update clients c1
>
> set c1.client_id =
>
> (select min(c2.client_id) from clients c2
>
> where (c2.firstname = c1.firstname) and
>
> (c2.middlename = c1.middlename) and
>
> (c2.lastname = c1.lastname))
>
>
>
> Tell me "Validation error for column client_id value "***null***"

More than likely, one of the name fields contains a null which you can't
test for equality on. You probably need something more like:

where (((c1.FirstName is null) and (c2.FirstName is null)) or
(c1.FirstName = c2.FirstName)) and
(((c1.MiddleName is null) and (c2.MiddleName is null)) or
(c1.MiddleName = c2.MiddleName)) and (((c1.LastName is null) and
(c2.LastName is null)) or (c1.LastName = c2.LastName))

When any field contains a null, you can't compare it to another field even
if that field is null as well. Null is an undefined state, not a value.

HTH

Woody (TMW)