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?
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
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
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
@col1and@col3values, the update will only update those two columns? Is this in a stored procedure or in C# directly - meaning, where/how does this query get generated and run? These factors will determine the answers.