Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
barflyz
Starting Member
47 Posts |
 Posted - 2010-05-14 : 13:52:22
|
| I have a table (table1) with a bunch of records say 50.this table has a code that is repeated in one column for the 50 records, example code is 123456I want to update/change code 123456 to a new code based on a list of distinct codes stored in a second table (table2) but evaluate and update one at a time, example belowexample: the first distinct code from table2 is code 456789, I want to take that code and update code 123456 in table1 to 456789 in table1 and then append these records from table1 to a 3rd table (table3)then repeat the process for each distinct code in table2, next codethe second distinct code from table2 is code 666666, I want to take that code and update code 456789 in table1 to 666666 in table1 and then append these records from table1 to a 3rd table (table3)and repeat, repeat, repeatI know you have to use a variable and loop but not that sure how to accomplish.thanks |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-14 : 14:53:06
|
quote:
and repeat, repeat, repeat
This is not the way to go...we need to look at a set-based solution. quote:
I want to update/change code 123456 to a new code based on a list of distinct codes stored in a second table (table2) but evaluate and update one at a time
This means table1 will have the last value of the distinct code form table2...I don't see the need to update table1? quote:
append these records from table1 to a 3rd table (table3)
So ideally what you want is all rows in table1 repeated that many number of times as the number of distinct codes you have? Is that right.So..consider this example.Table1 Datadeclare @table1 table (col1 int,col2 int,code int)insert @table1select 1,1,123456union all select 1,2,123456union all select 1,3,123456union all select 2,1,123456union all select 2,2,123456union all select 2,3,123456 Table2 Datadeclare @table2 table (code int)insert @table2select 456789union all select 666666union all select 888888union all select 456789 So when you run this...select a.col1,a.col2,b.codefrom @table1 a cross apply (select distinct code from @table2) b order by b.code you get thiscol1 col2 code----------- ----------- -----------1 1 4567891 2 4567891 3 4567892 1 4567892 2 4567892 3 4567891 1 6666661 2 6666661 3 6666662 1 6666662 2 6666662 3 6666661 1 8888881 2 8888881 3 8888882 1 8888882 2 8888882 3 888888 Is this the data you want inserted into table3?If not, please explain with sample data and expected output. |
 |
|
|
barflyz
Starting Member
47 Posts |
Posted - 2010-05-14 : 15:04:58
|
| "So ideally what you want is all rows in table1 repeated that many number of times as the number of distinct codes you have? Is that right." yes, but the codes have to be changed to the distinct codes also from the listI believe we are on the same page but I am just not sure how to code it.so table1 data ends up getting repeated like 2k+ times (2k+ distinct codes in table 2) * 50 rows and inserted into table 3 but the codes have to be changed for each block of 50 to each distinct chosen. I did something like this with looping and variables with unix and neteeza like 5 years ago but I am having trouble remembering thanks! |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-14 : 15:10:59
|
| Did you see my sample data?Table1 has 6 rows..and Table 2 has 3 distinct codes....when I run the select (also shown)...the 6 rows are repeated 3 times (once for each distinct code) ..SO you have 18 rows in total in the final result set.This is whatyou want inserted into Table3? |
|
|
|
barflyz
Starting Member
47 Posts |
Posted - 2010-05-14 : 15:48:44
|
| yes I believe so |
|
|
|
barflyz
Starting Member
47 Posts |
Posted - 2010-05-14 : 16:20:08
|
| if this is the code I am not sure I follow - |
|
|
|
|
|
|
|
|
update and insert