question

hdmiller avatar image
hdmiller asked

Sql question I moved my music from one drive to another. How do I update the database to show my new drive?

I've got a database of music and I added a new hard drive with more space. I simply need to update my DB to show this new path. It went from C:\Temp\Music\'random artists' to F:\Temp\Music\'random artists'


The issue is the 'random artists' is different for each song, and I can't make the set,replace,where,like to work.
sqlupdatesql database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered

Instead of worrying about the whole pattern, look at just what's the same / what's changed - the drive letter.

If, as you say, all that's happened is that you've moved from C:\ to F:\, then a simple REPLACE would do the job:

UPDATE MusicFiles 
SET Path = REPLACE(Path, 'C:\', 'F:\')
WHERE Path LIKE 'C:\%' -- optional

Alternatively, if you're moving from multiple drives to F:, but maintaining the paths, you could use the STUFF command:

UPDATE MusicFiles
SET Path = STUFF(Path, 1, 1, 'F')
WHERE Path NOT LIKE 'F%' -- again, optional

This will swap the first letter, no matter what it is...

Suggest you test these before you run, as I've not tested anything here...

You could also do something involving SUBSTRING or other LEFT/RIGHT functions, but they would be less readable. And, indeed, take longer to write... ;-)

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hdmiller avatar image hdmiller commented ·

Thank you for the reply BTW!

0 Likes 0 ·
hdmiller avatar image
hdmiller answered
update vintagelounge.songs
set path = replace(path, 'C:\', 'F:\')
where path like 'C:\%';

It's a mySQL database, that shouldn't matter right?

12:34:44update vintagelounge.songs set path = replace(path, 'C:\', 'F:\') where path like 'C:\%';Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'F:\') where path like 'C:\%'' at line 20.000 sec

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hdmiller avatar image hdmiller commented ·

It's weird because the 'C:\' gets underlined saying syntax missing 'comma'

0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ hdmiller commented ·

Hmm. Might be because it's MySQL it's doing something else weird. Try:

REPLACE(Path, 'C:', 'F:')

(ie without the backslashes...)

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.