Update Question

[LeoLionHeart]

A table is updated every morning via DTS.

I then use this table to update another table. My query is the following

Update tblPeople SET Email = (select b.mail FROM dim_user b WHERE loginID = b.NameLogin)

However, if the loginID is not in dim_user then the Email field will be NULL. However, there might previously be a Email in tblPeople (and I want this to stay). Is there a way to only update the Email field where the Email field is null.

Any help would be great.

 

[bborissov]

Update tblPeople SET Email = ISNULL((select b.mail FROM dim_user b WHERE loginID = b.NameLogin), Email)

If you want to update only NULL eMails:

Update tblPeople SET Email = (select b.mail FROM dim_user b WHERE loginID = b.NameLogin) WHERE EMail IS NULL

In code above I don't use ISNULL(...) becuase there is no diffrence if the Dim_User has no records for loginId eMail will be NULL.
By the way where loginId comes from?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.