Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-08
9
Medium Priority
?
338 Views
Last Modified: 2014-04-09
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
Comment
Question by:Aparanjith
9 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39987232
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
 

Author Comment

by:Aparanjith
ID: 39987532
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39987712
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Aparanjith
ID: 39987767
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39988544
>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
 
LVL 5

Expert Comment

by:jayakrishnabh
ID: 39988559
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
 

Author Comment

by:Aparanjith
ID: 39988648
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
 
LVL 5

Accepted Solution

by:
jayakrishnabh earned 1200 total points
ID: 39988678
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
 

Author Closing Comment

by:Aparanjith
ID: 39988836
Thanks for the query.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question