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
325 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

829 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