Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

I need to update the table where I may or may not update all columns, so how can I do that

Hi, I have a table which has some columns, in this table i am trying to update the table which is frequent process. So, here i may update some columns or all depending on my requirement for that particular time. So, now how can i write my update query here which involves all the columns, but if i do not need for particular column to update , then it should leave the value of the column as it is. i am little confused now how to do it, can I use stored procedure or with in the query? Can any one please let me know on this.
I am attaching the sample data I have for the table. Please let me know hw can I write the update query for it.
New-Microsoft-Excel-Worksheet.xlsx
0
Aparanjith
Asked:
Aparanjith
1 Solution
 
John_VidmarCommented:
Write a stored-procedure (SP) where you pass in some parameters.  Within the SP, you dynamically build the update-statement based on which parameters contain values.
0
 
AparanjithAuthor Commented:
Hi, john. I understood the logic you have told, but when executing my stored procedure, I may give may give only 2 parameter values, but where as in our input parameters there will be 5 parameters write, i am little confused in how to deal with it. Can anyone please explain
0
 
Anthony PerkinsCommented:
You have a choice:
1. Write an UPDATE for each potential combination or
2. Build some Dynamic SQL to do it on the fly in one UPDATE statement.  Just understand there are side-effects of using Dynamic SQL that you should be aware of.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AparanjithAuthor Commented:
I got the logic anthony, but how can i write the sql dynamically.

The columns I am using as shown in the excel has values as well as nulls, if i use stored procedure to this, I need to give all the input values in the execte syntax of SP. So, if I want to update only two columns in the table, then how can I use this SP as I am giving all input variables. At that point I am little confused, that how to write my query, where I can give only particular values as inputs, and if i do not give any values for another columns. it should keep the column as is. i am trying to build the my SP in such a way, that if i want to update 2 columns in the table then it should take that, later if I want to update 4 columns it should take that also. But only concern is how can i give the inputs as i have many columns.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>So, here i may update some columns or all depending on my requirement for that particular time.

Define 'for that particular time'.  T-SQL is a declarative language, so you'll have to spell this out in excrutiatingly clear language for your question to be actionable.
0
 
jayakrishnabhCommented:
One way of doing would be to create a procedure like below. You can pass the required column values and and the columns which are to be updated with the supplied values, else it would be same as before.

CREATE PROCEDURE Procedure_Name
(
@Column1Val int = 0,
@Column2Val int =0,
@Column1Update bit = 0,
@Column2Update bit = 0
)
AS
BEGIN

UPDATE
Table_Name
SET
Column1 = case when @Column1Update=1 then @Column1Val else Column1 end,
Column2 = case when @Column2Update=1 then @Column2Val else Column2 end

END
0
 
AparanjithAuthor Commented:
Hey jaya Krishna, I got the logic you have given. How do i give the execute statement here, if i only need column1 to update? Do i need to give all the 4 input values?
And i also have the where condition in my update, such as Id IN ('1321','32','222','323','44'), and how can I give the multiple values for where condition, struck in how to use the execute statement for this logic.
0
 
jayakrishnabhCommented:
If only column1 to be updated, just give @column1 param value and @Column1Update as 1 (true).
Where condition column can be passed as string with comma or any other delimiter. Inside the SP get it into a temp table from the string , select from the temp table in the where condition

for example..
declare @temp table (id int)
update table
set blah blah
where Id IN (select id from @temp)
0
 
AparanjithAuthor Commented:
Thanks for the query.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now