Sql 2005 Update Script Question

Question

Sql 2005 Update Script Question

Baronage12

Newbie

Points: 3
More actions
January 28, 2010 at 7:36 am

#219992

Hello all,
I'm relatively new the SQL and really know some of the basics. My question is:
I have a table with two columns. Part# and image path. There are about 400 records that have the image path F:/Pictures/<filename>
I need to change the image path drive letter from F to M. So all the records will be M:/Pictures/<filename> without changing the /Pictures/<filename>?
Can this be done?
Thank you so much in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Baronage12 Newbie Points: 3 More actions · Answer 2

I'm terribly sorry. New to this and all.

Table name is iitempath

Two columns: citemno which is the primary key (PK, char(20), not null)

and cimagepath (varchar(250), not null)

My Select statement looks like this:

Select citemno

,cimagepath

From iitempath

Where cimagepath like 'F:%'

Sample Query result looks like this:

citemno cimagepath

12321 F:\Pictures\DM1-372.jpg

The reason I have the Where clause because there are 580 records, but 415 have a image path name of

F:\Pictures\<filename>. I need to change the 415 records to have a image path name of M:\Pictures\<filename> . But I don't want to change the \Pictures\<filename> just the one letter F to M.

Desired Results:

citemno cimagepath

12321 M:\Pictures\DM1-372.jpg

Hope this helps...

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

I'm going to assume that you didn't read the article I referenced. Please take a minute or two and do so.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

321 MySQL Right there with Babe Points: 750 More actions · Answer 4

Baronage12 (1/28/2010)
I'm terribly sorry. New to this and all.
Table name is iitempath
Two columns: citemno which is the primary key (PK, char(20), not null)
and cimagepath (varchar(250), not null)
My Select statement looks like this:
Select citemno
,cimagepath
From iitempath
Where cimagepath like 'F:%'
Sample Query result looks like this:
citemno cimagepath
12321 F:\Pictures\DM1-372.jpg
The reason I have the Where clause because there are 580 records, but 415 have a image path name of
F:\Pictures\<filename>. I need to change the 415 records to have a image path name of M:\Pictures\<filename> . But I don't want to change the \Pictures\<filename> just the one letter F to M.
Desired Results:
citemno cimagepath
12321 M:\Pictures\DM1-372.jpg
Hope this helps...

very simple: update iitempath set cimagepath = replace (cimagepath,'F:/','M:/') where cimagepath like 'F:%'

but you should regard the notice from GilaMonster - the next time 😉

Baronage12 Newbie Points: 3 More actions · Answer 5

Thank you very much.

I will try and post more coherently next time.