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
317 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Aparanjith
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the query.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now