April 14, 2009 at 12:14 pm
Hello,
here is my short script in sql server 2000:
Create Table #tmp(ID int,y_val char(30),...)
Insert Into #tmp
exec stored_procedure
now my #tmp is filled with 214 rows,
87 of them have the same ID value(when I use distinct x_val
on #tmp I get 87 rows)
my next step is to try to update another table for each row(214 times)I have in the temporary table:
Update x_table
set field_x =
Case When m1.ID is null Then m2.x_val
Else m1.ID + ',' + m2.x_val End
From x_table m1
Inner join #tmp m2
where m1.ID= m2.ID
My problem is that the update action seems to run only 87 times and not 214 times as the number of rows in the #tmp table.
The m1.ID should have a value like this in the end: 25,27,30... for the rows with ID that exist more then once.
maybe this is more clearly sample:
#tmp table has rows like this:
ID val_1 val_2
1 25 26
1 42 31
2 55 25
3 60 30
Second table has this row:
ID val_1 val_2
1 42 31
2 55 25
3 60 30
I want the update action run 4 times as the number of rows in the #tmp
so row with ID 1 in the second table will be update twice.
Thanks for your help, Gil
April 14, 2009 at 12:49 pm
now my #tmp is filled with 214 rows,
87 of them have the same ID value(when I use distinct x_val
on #tmp I get 87 rows)
You have 87 unique values, not 87 rows with the same value here.
ID val_1 val_2
1 25 26
1 42 31
2 55 25
3 60 30
Second table has this row:
ID val_1 val_2
1 42 31
2 55 25
3 60 30
25,26,42,31
If so I don't know of any easy way to do this type of recursion in a single query. This seems like one of those times for dreaded cursor. Loop through each row in the temp table and run a single update. Or maybe I am missing the desired output.
Sean
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 12:59 pm
Would this work, or are there already values in x_table that you also have to take into account?
--ID val_1 val_2
--1 25 26
--1 42 31
--2 55 25
--3 60 30
--
--
--Second table has this row:
--ID val_1 val_2
--1 42 31
--2 55 25
--3 60 30
create table dbo.xtable (
ID int,
val1 int null,
val2 int null
);
create table #tmp (
ID int,
val1 int,
val2 int
);
insert into #tmp
select 1, 25, 26 union all
select 1, 42, 31 union all
select 2, 55, 25 union all
select 3, 60, 30
;
insert into dbo.xtable (ID, val1, val2)
select
ID,
sum(val1),
sum(val2)
from
#tmp
group by
ID;
select * from dbo.xtable;
drop table dbo.xtable; -- drop table from MY SandBox database
drop table #tmp; -- drop table from MY SandBox database
April 14, 2009 at 1:06 pm
From the original post it looks as though he wants the values concatenated with a ',' instead of just a sum. Otherwise it would be too easy. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 1:17 pm
slange (4/14/2009)
From the original post it looks as though he wants the values concatenated with a ',' instead of just a sum. Otherwise it would be too easy. 😉
Okay, I miss read the post, my mistake. Of course, it would also help if the OP would provide DDL for the tables, sample data in a consumable format, and actual expected resulst in a format that made things understandable easily.
Just need a few changes, but it is do able. Question still remains, however, do I need to worry about preexisting data in the final table.
April 14, 2009 at 1:24 pm
Okay, try this instead:
create table dbo.xtable (
ID int,
val1 varchar(30) null,
val2 varchar(30) null
);
create table #tmp (
ID int,
val1 varchar(30) null,
val2 varchar(30) null
);
insert into #tmp
select 1, '25', '26' union all
select 1, '42', '31' union all
select 2, '55', '25' union all
select 3, '60', '30'
;
insert into dbo.xtable (ID, val1, val2)
select distinct
ID,
stuff((select ',' + t1.val1 from #tmp t1 where t1.ID = t.ID for xml path ('')),1,1,''),
stuff((select ',' + t1.val2 from #tmp t1 where t1.ID = t.ID for xml path ('')),1,1,'')
from
#tmp t
;
select * from dbo.xtable;
drop table dbo.xtable;
drop table #tmp;
April 14, 2009 at 2:13 pm
10ks for your replies.
I need to use update and not insert to.
I will try to explain myself better.
this is my tmp table:
ID val_1 val_2
1 somthing 26
1 somthing 31
2 somthing 25
3 somthing 30
1 somthing 79
Second table has this row:
ID val_1 val_2
1 somthing null
2 somthing null
3 somthing null
after this update:
Update _table
set val_2 =
Case When m1.val_2 is null Then m2.val_2
Else m1.val_2 + ',' + m2.val_2 End
From _table m1
Inner join #tmp m2
where m1.ID= m2.ID
The second table _table should look like this afeter the update:
ID val_1 val_2
1 somthing 26,31,79
2 somthing 25
3 somthing 30
but what I get is:
1 somthing 79
2 somthing 25
3 somthing 30
row with ID = 1 is update only once with the last value.
April 14, 2009 at 3:03 pm
Still confused. One, the formatting doesn't allow me to easily see what is in what column. Also, Are we updating existing data? Your original post really did not make it that clear.
It would help if you provided a complete set of DDL (CREATE TABLE statements), sample data (same format I used in the code I posted), and well formatted expected results so that it is easy to determine if the code is correct.
April 14, 2009 at 3:14 pm
unfortunately I am writing from home and have no Internet at work.
Yes , I need to update exsiting data
April 14, 2009 at 3:25 pm
gbechar (4/14/2009)
unfortunately I am writing from home and have no Internet at work.Yes , I need to update exsiting data
Well, you need to provide a clearer picture of what you need. I can't tell what needs to be updated and what is existing data. Are you updating two columns or one? Your last post it "appeared" to be updating one column (var2), but your previous posts seemed to indicate two columns (var1 and var2).
April 14, 2009 at 3:30 pm
Does this help?
create table dbo.xtable (
ID int,
val1 varchar(30) null,
val2 varchar(30) null
);
create table #tmp (
ID int,
val1 varchar(30) null,
val2 varchar(30) null
);
insert into #tmp
select 1, '25', '26' union all
select 1, '42', '31' union all
select 2, '55', '25' union all
select 3, '60', '30'
;
insert into dbo.xtable (ID)
select distinct ID from #tmp;
select * from dbo.xtable;
update dbo.xtable set
val1 = isnull(xt.val1 + ',','') + stuff((select ',' + t1.val1 from #tmp t1 where t1.ID = t.ID for xml path ('')),1,1,''),
val2 = isnull(xt.val2 + ',','') + stuff((select ',' + t1.val2 from #tmp t1 where t1.ID = t.ID for xml path ('')),1,1,'')
from
dbo.xtable xt
inner join #tmp t
on (xt.ID = t.ID)
;
select * from dbo.xtable;
drop table dbo.xtable;
drop table #tmp;
April 14, 2009 at 3:33 pm
May I suggest you read this article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Also you may want to read this blog: The Flip Side
April 14, 2009 at 3:48 pm
I need to update only one column in my second table, for each row in the tmp table I should concatenate ',' + the value from the field from the current row in the tmp table.
April 14, 2009 at 3:54 pm
I appreciate the help in this forum, and I'm sorry I can't show more precisely info about my problem, like I sayed before, no Internet at my job.
April 14, 2009 at 4:04 pm
You should still be able to use the code sample above to develop a solution to your problem at work.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply