CODEWITHSUNDEEP
sqlsql-server-2005sql-update
Brian Bolton
Brian Bolton

Reputation: 3642

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.

Upvotes: 0

Views: 148

Answers (3)

mbeckish
mbeckish

Reputation: 10579

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)

Upvotes: 0

Welbog
Welbog

Reputation: 60398

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)

Upvotes: 0

Restore The Data Dumps Again
Restore The Data Dumps Again

Reputation: 39325

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 <> ''

Upvotes: 5

Related Questions