Updating a DB table excluding NULLs

I have two tables with the same columns

tbl_source (ID, Title)

tbl_dest (ID, Title)

I want to update tbl_dest titles from the tbl_source where the ids in dest and source match. However, I don't want to update the dest title if the source title is null (or blank).

I've got this:

UPDATE    tbl_dest
SET              tbl_dest.Title =
      (SELECT     title
        FROM          tbl_source
        WHERE      tbl_dest.id = tbl_source.ID and tbl_source.title is not null)

But it keeps inserting the nulls.

How would I construct such a query?

I am using SQL server 2005.

Thanks.

From stackoverflow

• Use an inner join...

  Update tbl_dest
  Set tbl_dest.Title = tbl_source.Title
  From tbl_dest inner join tbl_source on tbl_dest.ID = tbl_source.ID
  Where tbl_source.Title is not null and tbl_source.Title <> ''
  

  Brian Bolton : That did the trick. Thanks.

• It's setting the value to null because the subquery is returning null, and you're not filtering records in your update clause.

  Try something like this instead:

  UPDATE tbl_dest
  SET tbl_dest.Title = 
      (SELECT title
      FROM tbl_source
      WHERE tbl_source.id = tbl_dest.id)
  WHERE EXISTS
      (SELECT 1
      FROM tbl_source
      WHERE tbl_source.id = tbl_dest.id
      AND tbl_source.title IS NOT NULL)
  

• That's because the outer query is updating every record (no WHERE clause), so when the inner query finds no matching record, NULL gets inserted.

  Add a WHERE clause to the outer query to not do an update on those records:

  UPDATE    tbl_dest
  SET              tbl_dest.Title =
        (SELECT     title
          FROM          tbl_source
          WHERE      tbl_dest.id = tbl_source.ID and tbl_source.title is not null)
  WHERE EXISTS
        (SELECT     title
          FROM          tbl_source
          WHERE      tbl_dest.id = tbl_source.ID and tbl_source.title is not null)