SQL Server Updating Multiple Columns

Question

I have a script that updates multiple columns. However, I want to ensure that the columns in the UPDATE list are only updated when they are NULL. Here is the script:

DECLARE @blank nvarchar (255) = '';
UPDATE Table
SET
    Column1 = @blank,
    Column2 = @blank,
    Column3 = @blank
WHERE
    Column1 IS NULL OR
    Column2 IS NULL OR
    Column3 IS NULL

This will not work, because all the columns will be updated even if only Column1 is null.

I need to only update column values if that value is NULL.

Gordon Linoff · Accepted Answer · 2013-06-18 15:05:08Z

5

You can use conditional updates:

update table
    set Column1 = coalesce(Column1, @blank),
        Column2 = coalesce(Column2, @blank),
        Column3 = coalesce(Column3, @blank)
    where Column1 IS NULL OR
          Column2 IS NULL OR
          Column3 IS NULL;

If the column value is not null, then the original value is assigned. Otherwise, the blank value is assigned.

answered Jun 18, 2013 at 15:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Updating Multiple Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related