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
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

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 65

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 400 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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