update multiple columns with one condition in sql server

Question

I have one table with some columns and I need to update it on conditional basis in SQL server. I have one table and I wanted to execute the 'Update' statement on this table after some UI operation. But the thing is I have to update only some columns on a condition basis and some columns will be updated without any condition.

Please check the schema details below

Table_1

ID	Name	Email	Phone
1	A	[email protected]	12345678890
2	B	[email protected]	14345678890

I have one procedure where we are sending the parameters for updating this table and we are also sending one flag parameter (for applying the condition in update statement)

CREATE PROC UpdateStudent                    
 @Name VARCHAR(100) = NULL,                    
 @Email VARCHAR(100) = NULL,                 
 @Phone VARCHAR(20) = NULL,                   
 @IsChanged BIT = NULL 
AS                   
BEGIN                         
           
      --update 'Table_1'
      update Table_1 SET 
                 Name = IIF(@IsChanged = 1, @Name, null),
                 Email = @Email,
                 Phone = IIF(@IsChanged = 1, @Phone , null)
      where ID = 1
END

So if flag @IsChanged is 1 then as per the above query I need to update but if flag @IsChanged is 0 then I have to update different 'Update' statement-

  IF @IsChanged = 0
  BEGIN
              --update 'Table_1'
                update Table_1 SET 
                    Name = NULL,
                    Email = NULL,
                    Phone = NULL
               where ID = 1

  END

Is there any best way to handle this requirement where if we have to update multiple columns with one condition, how we can do that in SQL server?

@TimBiegeleisen, nothing wrong, but the thing is if I have 10 columns in my table then I have to use this condition 10 times for each columns. So, I am looking for a way where we can use one case statement or one condition only to update it. — sunil
– sunil, Commented Apr 26, 2021 at 10:57
You could replace IIF with a CASE expression, but it would actually give you more code, not less. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 26, 2021 at 10:58
@TimBiegeleisen, will it resolve my problem? If yes please add some code. — sunil
– sunil, Commented Apr 26, 2021 at 11:14
No - there is no "problem" to solve here. You are simply trying to avoid writing repetitive code. Asking others to do it for you for free seems more than a bit presumptuous. And I think your current design and logic make no sense. If nothing has changed (IsChanged = 0), then why would this procedure be called? And default values of null for a procedure like this make no sense IMO but do encourage developer laziness. — SMor
– SMor, Commented Apr 26, 2021 at 11:24

Venkataraman R · Accepted Answer · 2021-04-26 11:27:45Z

1

To make things more clear, you can have IF else logic.

IF @IsChanged = 1 
BEGIN 
update Table_1 SET 
                 Name = @Name
                 Email = @Email,
                 Phone = @Phone
      where ID = 1
END
ELSE
BEGIN
update Table_1 SET 
                 Name = null
                 Email = @Email,
                 Phone =  null
      where ID = 1
END

answered Apr 26, 2021 at 11:27

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

update multiple columns with one condition in sql server

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