CODEWITHSUNDEEP
c#sql-servert-sql
Meidi
Meidi

Reputation: 572

Update certain columns in sql

I have an update query like this:

update
set
column1 = @col1,
column2 = @col2,
...
column10 = @col10
from table
where table.id=@col0

I only need to update certain columns depends on user's input. How do I skip certain columns when passing the values into this query?

Upvotes: 0

Views: 328

Answers (3)

dima
dima

Reputation: 1189

just use IsNull() for it:

update
set
column1 = IsNull(@col1,column1),
column2 = IsNull(@col2,column2),
...
column10 = IsNull(@col10,column10)
from table
where table.id=@col0

what it would to, it would check if the variable (@col1, @col2 etc) is NULL and if so, update the column to its current value, otherwise update to variable's value. It'll work as long as missing variables are nulls

Upvotes: 0

user3188781
user3188781

Reputation:

You can use case in the update statement. Hope this helps:

Let's assume your table is as below:

create table tblTest
(
  ID int,
  column1 varchar(10),
  column2 varchar(10),
  column3 varchar(10),
  column4 varchar(10),
  column5 varchar(10)
)

Insert values for test:

Insert into tblTest (ID, Column1,Column2,Column3,Column4, Column5) values (1,'a','b','c','d','e')

Set up variables for test. 

  declare @ID int
  declare @col1 varchar(10)
  declare @col2 varchar(10)
  declare @col3 varchar(10)
  declare @col4 varchar(10)
  declare @col5 varchar(10)

  set @ID = 1
  set @col1 = 'a'
  set @col2 = ''
  set @col3 = 'y'
  set @col4 = 'd'
  set @col5 = 'z'

  update tblTest 
    set 
        column1 = case when LEN(@col1) > 0 then @col1 else column1 end,
        column2 = case when LEN(@col2) > 0 then @col2 else column2 end ,
        column3 = case when LEN(@col3) > 0 then @col3 else column3 end ,
        column4 = case when LEN(@col4) > 0 then @col4 else column4 end ,
        column5 = case when LEN(@col5) > 0 then @col5 else column5 end 
    where
        ID = @ID



select * from tblTest

I've created a sample in SQL Fiddle here: http://sqlfiddle.com/#!3/e666d/6

Upvotes: 2

JesalynOlson
JesalynOlson

Reputation: 513

I would recommend that you can either send them as case statements so if you are passing @col1 as NULL or '' then you can do 

update
set
column1 = CASE WHEN ISNULL(@col1,'')='' THEN column1 ELSE @col1 END,,
column2 = CASE WHEN ISNULL(@col2,'')='' THEN column2 ELSE @col2 END,,
...
column10 = CASE WHEN ISNULL(@col10,'')='' THEN column10 ELSE @col10 END,
from table
where table.id=@col0

Upvotes: 0

Related Questions