CODEWITHSUNDEEP
sql-servert-sql
Koo SengSeng
Koo SengSeng

Reputation: 953

T-SQL: Update column only if value is provided

Currently I have this UPDATE statement:

UPDATE Customer 
SET Name = @Name,
    Age = @Age,
    Email = @Email
    ...

Now I need to include column into the UPDATE statement only if value is provided. Otherwise, do not include the column in the UPDATE statement.

So conceptually probably something like this:

UPDATE Customer 
SET
    IF LEN(@Name) > 0 THEN Name = @Name,
    IF LEN(@Age) > 0 THEN Age = @Age,
    IF LEN(@Email ) > 0 THEN Email = @Email
    ...

Is there any way can achieve this? I'm thinking about dynamic query but the list of update columns are super long, so tons of work need to be done to change to dynamic query like this:

SET @SQL = 'UPDATE Customer SET '

IF LEN(@Name) > 0
    SET @SQL = @SQL + 'Name = @Name,'

IF LEN(@Age) > 0
    SET @SQL = @SQL + 'Age= @Age,'

Upvotes: 4

Views: 4458

Answers (4)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can also try this

UPDATE Customer SET
    [Name] = ISNULL(@Name, [Name])
    ,Age = ISNULL(@Age, Age)
    ,Email = ISNULL(@Email, Email)
...

It is a shorthand query of checking NULL value in update.

Upvotes: 1

e_i_pi
e_i_pi

Reputation: 4820

If your missing values are null:

UPDATE [Customer] SET
    [Name] = COALESCE(@Name, [Name]),
    [Age] = COALESCE(@Age, [Age]),
    [Email] = COALESCE(@Email, [Email]),
    ...

If they're blank, then you can use NULLIF to cast to null, which has the potential added benefit of treating empty strings the same as nulls:

UPDATE [Customer] SET
    [Name] = COALESCE(NULLIF(@Name, ''), [Name]),
    [Age] = COALESCE(NULLIF(@Age, ''), [Age]),
    [Email] = COALESCE(NULLIF(@Email, ''), [Email]),
    ...

Upvotes: 1

Omar Hamdan
Omar Hamdan

Reputation: 192

I think you should try this (after declaring and setting your variables @Name, @Age and @Email )

    UPDATE Customer SET
    Name = case when @Name  is null then Name  else @Name  end,
    Age  = case when @Age   is null then Age   else @Age   end,
    Email= case when @Email is null then Email else @Email end,
    ...

if any of the variables has a null value it will not update the field.

Upvotes: 5

CSharp821
CSharp821

Reputation: 350

What about something like this...

UPDATE Customer SET
    [Name] = CASE WHEN ISNULL(@Name, '') = '' THEN [Name] ELSE @Name END
    ,Age = CASE WHEN ISNULL(@Age, '') = '' THEN Age ELSE @Age END
    ,Email = CASE WHEN ISNULL(@Email, '') = '' THEN Email ELSE @Email END
    ...

Upvotes: 3

Related Questions