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 |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2005-12-14 : 15:43:57
|
| Hi,We have a nvarchar column with values as following:256/3586211/256/3586203/211/256/3586/203/211/256/3586We need to update this column and add 100 to all this values so after update the result will look like:356/3686311/356/3686303/311/356/3686/303/311/356/3686How we can write this update statement as we want to update all this rows(5M) in a single batch?Thanks--rubs |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-14 : 16:16:51
|
| Are there any number of integers separated by / or a maximum of 4? |
 |
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-12-14 : 16:19:26
|
| Rows have 5 columns and this is one of the column. The complete row looks like:256 3586 1 N 2005-12-13 00:00:00.000 2038-01-01 00:00:00.000 256/3586211 3586 2 N 2005-12-13 00:00:00.000 2038-01-01 00:00:00.000 211/256/3586203 3586 3 N 2005-12-13 00:00:00.000 2038-01-01 00:00:00.000 203/211/256/35861 3586 4 N 2005-12-13 00:00:00.000 2038-01-01 00:00:00.000 /203/211/256/3586For this particular column maximum no of int seperated by / are 5 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-14 : 16:24:44
|
| wow rubs, this is really ugly. Just updating 5 mil rows in a single shot is ugly enough but you'll need to parse the values for each row while you're doing it.do you have room to improvise? like:Create another table with the parsed values in a single numeric column?orIn your sample data you only have a few distinct values (356,3686,311,etc) is this the case for all 5 million rows? in other words can you do a nested replace?ie: replace(replace(replace(col, '356', '456'), '3686', '3786'), '311', '411')Be One with the OptimizerTG |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-14 : 16:29:21
|
| Is there any pattern of the data u provided in the first thread?eg. Last portion has always 4 digits ? Until the last one there may be o digits or 3 digits ? numbers not exceeding some value ?This may need functions as charindex, substring, replace, isnumeric etc. |
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-12-14 : 16:37:31
|
| This column is basically contains all the mapping of a child to its parents at all levels, there is no definate pattern and values are preety different for each child.Is there any easy way i can divide this column into 5 int columns in a seperate table and then update them and bring back.Thanks--rubs |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-14 : 23:35:17
|
quote:
Originally posted by rubs_65
Is there any easy way i can divide this column into 5 int columns in a seperate table and then update them and bring back.
It isn't easy, it's messy. I'd suggest parsing the numbers into a temp table of integers, do the math, then update the original table with the result. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-12-15 : 00:06:44
|
| Sam is on the money rubs. Basically you're going to have to cursor through your records to do the update (whether in T-SQL or in your application tier). Sounds like a one-off function, so write an app and press "go" before you leave for the weekend. For ease, you could do it via DTS, but whatever, its going to be SLOOOOOOOW.Hierarchies are the worst case for SQL and this is definitely NOT a great way to store them.--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-15 : 08:50:43
|
| U can create additional columns (say 5) to hold the integer values.I'll give u the Select Query, from which u can deduct an Update. [First u'd be able to c the results]Here the field Name F1 is the column that contain ur concerned data & T3 is the table name.Select F1, SUBSTRING (F1 , 1 , CHARINDEX ( '/' , F1 , 1 )-1 ), right(F1, Len(F1)-CHARINDEX ( '/' , F1 , 1 )) from T3Steps:1. First make a backup copy of existing table Select * into bkupTbl from MyTbl2. Create a column to manipulate data of F1 and update that column with data in F1 Update T3 set NewCol = F13. Create 5 additional columns for Data (D1,D2,D3,D4 & D5) -- May be more if u have more than 5 data elements seperated by '/'4. Update T3 set NewCol = right(F1, Len(F1)-CHARINDEX ( '/' , F1 , 1 )), D1 = SUBSTRING (F1 , 1 , CHARINDEX ( '/' , F1 , 1 )-1 ) 5. Update T3 set NewCol = right(NewCol, Len(NewCol)-CHARINDEX ( '/' , NewCol , 1 )), D2 = SUBSTRING (NewCol , 1 , CHARINDEX ( '/' , NewCol , 1 )-1 ) 6. Update T3 set NewCol = right(NewCol, Len(NewCol)-CHARINDEX ( '/' , NewCol , 1 )), D3 = SUBSTRING (NewCol , 1 , CHARINDEX ( '/' , NewCol , 1 )-1 ) 7. Update T3 set NewCol = right(NewCol, Len(NewCol)-CHARINDEX ( '/' , NewCol , 1 )), D4 = SUBSTRING (NewCol , 1 , CHARINDEX ( '/' , NewCol , 1 )-1 ) 8. Update T3 set NewCol = right(NewCol, Len(NewCol)-CHARINDEX ( '/' , NewCol , 1 )), D5 = SUBSTRING (NewCol , 1 , CHARINDEX ( '/' , NewCol , 1 )-1 ) By this way u'll be able to split ur data into several fields, but I didn't test these.Also, even if u deal with a cursor (as rrb suggests), the same string functions I used would be useful.That way takes a long time but can visualize (I mean less error prone) how the program (rather a stored procedure) works. |
|
|
|
|
|
|
|
|
query problem