February 18, 2009 at 1:41 pm
Hi All,
Can you please let me know how do we achieve it in Sql Server.
I have a table T1 with Columns C1 And C2.
I need to update Table T1 such that C1 value gets updated for C2 value.
Table before Update
C1 C2
15 5
25 4
3 7
48 12
92 112
The input for this stored proc is like this C1 = (1,3,5,7,9) and C2= (5,4,7,12,112). Comma Separated list of values
After Update Table should hold data like this
C1 C2
1 5
3 4
5 7
7 12
9 112
So how do I achieve this in a single Stored Proc call.
Please let me know if you need any other info or if my problem statement is not clear
February 18, 2009 at 5:13 pm
Hi,
Can I just clarify your requirements here - do you have a stored proc with two parameters. One parameter will give the new values for Column1 and the second parameter will give the new values for column2?
Will the number of values in the two csv lists always be the same? And always enough to fill the whole table?
B
February 18, 2009 at 5:42 pm
Hi
Thanks for your enquiry.
This is the table A with 2 columns of int type.
StoreId Qty
3 5
6 7
84 12
89 45
and so on.....
Now I need to update the Qty column based on the storeId value.
I pass parameters as StoredId list = (3,84,89) and Qty list = (12, 56, 67)..
So after executing the Stored proc I should have values in my table like this
StoreId Qty
3 12
6 7
84 56
89 67
Those values are not always constant. From the front end web app I check what all values have been changed and build the comma delimited string as follows like StoreId List and QtyList. Now I just want to update my table in one call.
I hope the reqmnt is clear. If not please let me know.
February 18, 2009 at 6:43 pm
Hi.
I think it would be best to pass your data in a different format. Something more like '3,12;84,56;89,67'. ie, a string of ID/Value pairs.
But, besides that, here is a workable solution.
First of all you will need to add a function that splits a csv string into a table. This sort of thing has been documented quite well on the site and especially by Jeff Moden...
You will need a tally table for this function to work:
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
CREATE FUNCTION [dbo].[SplitList]
(
@List varchar(8000),
@splitter varchar(2)
)
RETURNS
@ParsedList table
(
num int identity(1,1),
item varchar(1024)
)
AS
BEGIN
SET @List = @splitter + LTRIM(RTRIM(@List)) + @splitter
INSERT INTO @ParsedList (item)
SELECT SUBSTRING(@List,N+1,CHARINDEX(@splitter,@List,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@List) AND SUBSTRING(@List,N,1) = @splitter
RETURN
END
And once you have your tally table and your splitList function...
create table #tableA (storeID int, qty int)
insert #tableA
select 3, 5
union all select 6, 7
union all select 84,12
union all select 89,45
declare @StoreIDList varchar(1024)
declare @QtyList varchar(1024)
set @StoreIDList = '3,84,89'
set @QtyList = '12, 56, 67'
-- Have a look at the joined data...
select s.item as store, q.item as quantity
from splitlist(@StoreIDList, ',') s
inner join splitlist(@QtyList, ',') q on s.num = q.num
inner join #tableA a on s.item = a.storeID
-- Update tableA
UPDATE #tableA
SET #tableA.qty = q.item
from splitlist(@StoreIDList, ',') s
inner join splitlist(@QtyList, ',') q on s.num = q.num
inner join #tableA a on s.item = a.storeID
-- The updated data
select * from #tableA
That can easily be converted into a function.
B
February 19, 2009 at 11:47 am
Hi B
Thank you so much for the help. I was able to make it work.
You are wonderful.
Thanks once again.
Sudhakar
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply