hard question.. is it possible to update many column of data in 1 query?

in my original database have a column which is for "path" ,the record in this column is like → 【mms://192.12.34.56/2/1/kbe-1a1.wmv】

this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..

I have to change 【 mms://192.12.34.56/2/1/kbe-1a1.wav】 to 【 mms://202.11.34.56/2/1/kbe-1a1.wav】

I tried to find the reference book and internet . can't find out the answer for this problem.

can you help? or maybe is it a impossible job?

thanks

As long as you're using an exact match it shouldn't be an issue. (UPDATE TableName SET FieldName = NewFieldValue WHERE FieldName = OldFieldValue)

But, if you're talking about have to fix the path for a lot of different files, you'll have to do either a complex sproc that can parse the path or a small application that pulls the initial set using a LIKE query for the path to be changed and then turns around and does an UPDATE on each record to the new path.

|||

thank you

problem is to fix the path but I can't really understand your suggest

can you explain more or just give me a simple example please

I appreciate your help, thank you very much

|||

You can use REPLACE to fix your path value. Here is a sample:

UPDATE pathToChangeSET path =replace(path,'192.12','202.11')

It will replace all 192.12 with 202.11 if you run this query for your table.

|||

thank you

sorry I didn't make my question clearly ..

the problem can't be resolved by use replace

for example

in the same column " voice"

some are looks like → mms:// 1.2.3.4/1/2/voice.wma

some are looks like → mms://www.showhigh.com/1/2/voice.wma

also we have others like → "this voice is mms://1.2.3.4/1/2/voice.wma " -- the last kind of data we don't know how many cheracter front of the path...

-----------

do you have any good idea to change the path without manually

thanks again..

|||

Do you want to change mms:// 1.2.3.4/, mms://www.showhigh.com/, and this voice is mms://1.2.3.4/ or anything up to the first slash (/) to mms://202.11.34.56/? If this is the case, you need to find the location of this slash and get this job done with a string operation.

Post some sample data and the result you are expecting. It will save time to get result. Thanks.

|||

sorry.. not make my question clearly enough still..

here are the example for my question..

in Table name Movie, have a column name "Movie_Voice"

Voice_no | Movie_Voice

1 | mms://1.2.3.4/1/65/05-65-01.wma want change it to be → mms/1.2.3.4/1/65/05-65-01.wma

2 | mms://media.movie.com/12/79/ef13-79-01.wma want change it to be → mms/12/79/ef13-79-01.wma

3 | there are the voice path mms://1.2.3.4/23/230.wma want change it to be → there are the voice path mms/1.2.3.4/23/230.wma

4 | your voice path mms://media.movie.com/98/2/23.wma want change it to be → your voice path mms/98/2/23.wma

------------------------------------------------------------

the voice_no 3 and voice_no 4 are the hardest part for me to think out a way to resolve ,because I don't know what are front of the mms path

the reason why I have to change the data in database . I move all wma files to root and creat a file name mms, so now I have to change all path

which alreday list in database...

thank you

|||

Hi jc,

If these are the only patterns or u have more patterns. If you can provide me a list of pattern i can create a regex to update the column for you which you can use to update the column

Satya

|||

unfortunately... I can't list a pattern for this problem

because... the pattern may be up to 4000 or more...

some of tables which including all html tag to 1 column . the column not have the mms path not only have some html tag ..

|||

Can u send me the exported table in a txt file or a mdf file

satya.tanwar@.gmail.com

coz without seeing the patterns nobody will be able to produce a desired solution

Satya

|||

thank you , I have just sent !

|||

Hi Try this function,

Select dbo.PatternReplace(TestColumn,'%mms://%/','mms/'),TestColumnfrom pro_Lesson_Text

CREATEFUNCTION dbo.PatternReplace

(

@.InputStringNVARCHAR(MAX),

@.PatternVARCHAR(100),

@.ReplaceTextNVARCHAR(MAX)

)

RETURNSNVARCHAR(MAX)

AS

BEGIN

DECLARE @.ResultNVARCHAR(MAX)SET @.Result=''

-- First character in a match

DECLARE @.FirstINT

-- Next character to start search on

DECLARE @.NextINTSET @.Next= 1

-- Length of the total string -- 8001 if @.InputString is NULL

DECLARE @.LenINTSET @.Len=COALESCE(LEN(@.InputString), 8001)

-- End of a pattern

DECLARE @.EndPatternINT

WHILE(@.Next<= @.Len)

BEGIN

SET @.First=PATINDEX('%'+ @.Pattern+'%',SUBSTRING(@.InputString, @.Next, @.Len))

IFCOALESCE(@.First, 0)= 0--no match - return

BEGIN

SET @.Result= @.Result+

CASE--return NULL, just like REPLACE, if inputs are NULL

WHEN @.InputStringISNULL

OR @.PatternISNULL

OR @.ReplaceTextISNULLTHENNULL

ELSESUBSTRING(@.InputString, @.Next, @.Len)

END

BREAK

END

ELSE

BEGIN

-- Concatenate characters before the match to the result

SET @.Result= @.Result+SUBSTRING(@.InputString, @.Next, @.First- 1)

SET @.Next= @.Next+ @.First- 1

SET @.EndPattern= 1

-- Find start of end pattern range

WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))= 0SET @.EndPattern= @.EndPattern+ 1

-- Find end of pattern range

WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))> 0

AND @.Len>=(@.Next+ @.EndPattern- 1)

SET @.EndPattern= @.EndPattern+ 1

--Either at the end of the pattern or @.Next + @.EndPattern = @.Len

SET @.Result= @.Result+ @.ReplaceTextSET @.Next= @.Next+ @.EndPattern- 1

END

END

RETURN(@.Result)

END

Result looks good Check my mail for the Results. Let me know in case anything else is required

Satya

|||

It looks like this would do it as well:

UPDATE Movie

SET Movie_voice=REPLACE(REPLACE(Movie_voice,'mms://media.movie.com/','mms/'),'mms://','mms/')

|||

Hi motley,

Its a good try but the problem is he has different patterns in the data to update. So need to update patterns not static string.

satya

|||

Maybe I misunderstood what he was asking for, but wouldn't the code you gave change:

mms://1.2.3.4/stuff to mms/stufff? And that's not what he showed what he wanted for #1 & #3.