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.
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
Hmm. Might be because it's MySQL it's doing something else weird. Try:
REPLACE(Path, 'C:', 'F:')
(ie without the backslashes...)
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... ;-)
