Mr.Potkin Mr.Potkin - 1 month ago 13
SQL Question

How to combine 3 updates into one

I have this 3 updates

UPDATE [Person]
SET [Email] = @Prefix
WHERE [Id] = @PersonId AND ISNULL([Email], '') <> ''

UPDATE [Person]
SET [Phone] = @Phone
WHERE [Id] = @PersonId AND ISNULL([Phone], '') <> ''

UPDATE [Person]
SET [Skype] = @Skype
WHERE [Id] = @PersonId AND ISNULL([Skype], '') <> ''


Can I combine it into one or how can I write more performance operation? Thanks

Answer

I mean, you can combine them:

UPDATE [Person]
SET [Email] = CASE WHEN ISNULL([Email], '') <> '' THEN @Prefix ELSE [Email] END,
    [Phone] = CASE WHEN ISNULL([Phone], '') <> '' THEN @Phone ELSE [Phone] END,
    [Skype] = CASE WHEN ISNULL([Skype], '') <> '' THEN @Skype ELSE [Skype] END
WHERE [Id] = @PersonId 
AND (ISNULL([Email], '') <> '' OR ISNULL([Phone], '') <> '' OR ISNULL([Skype], '') <> '');

Not sure if this will actually improve performance though