SQL UPDATE query.

[neti1987]

hi!

I hope this is the place for my my question:

I work on a file tagging system. I write it in java and use MySQL as my DB.
I have a table that contain only one field: file's path.

when directory is renamed, it's path changes. and that's mean, that all the files in the system, which are in the directory (or in its sub-directories), have false path.

in such case, I want to update all the files which start with the changed directory path.
but the update should be replacing the beginning of all thus paths, with the new beginning (to replace the old directory name in the new, but in the middle of another paths).

how could I do that? how could I make it the most efficient?

thanks,
Nate

[griswolf]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html should give you some ideas

[qualitybrains]

I hope following will help you !

SELECT REPLACE('c:/olddirectory/subdirectory/filename.extension', 'olddirectory', 'newdirectory');

Vivi

[neti1987]

I hope following will help you !

SELECT REPLACE('c:/olddirectory/subdirectory/filename.extension', 'olddirectory', 'newdirectory');

Vivi

that's nice, but how could I update all the table's values?

[griswolf]

neti1987: I really think you should do at least some of the work for yourself. You want to use UPDATE syntax. If you have not yet found the MySql documentation online, look here: http://dev.mysql.com/doc/refman/5.0/en/ It is official, complete, reasonably clear and searchable.

[qualitybrains]

Use :

update TableName set PathColumnName=replace(PathColumnName, 'olddirectory','newdirectory');

Simple :)
Vivi
First, solve the problem. Them write the Code

[neti1987]

Use :

update TableName set PathColumnName=replace(PathColumnName, 'olddirectory','newdirectory');

Simple :)
Vivi
First, solve the problem. Them write the Code

Thanks!

first I did it using java resultset. but I think it isn't efficient.

I guess your offer, is better, so thanks again!

only last question:
could u think about even more efficient way to do so?

-Nate

[qualitybrains]

Nate, if it helps you it is more than enough. It's quite a small line. There is always a chance of improvement but the effort required should be worth paying :) I wish it was able to solve your purpose.

[tyson.crouch]

the method posted by qualitybrains is the most efficient.

This is because rather than:
1. pulling data into java
2. modifying data
3. updating in mysql

All your doing is basically step 3. this is because the MySQL engine handles steps 1 and 2 more or less for you.

[qualitybrains]

Thanks Tyson

Vivi

[tyson.crouch]

Thanks Tyson

Vivi

=D no problem

[neti1987]

the method posted by qualitybrains is the most efficient.

This is because rather than:
1. pulling data into java
2. modifying data
3. updating in mysql

All your doing is basically step 3. this is because the MySQL engine handles steps 1 and 2 more or less for you.

Like I thought and hoped!
Thanks!
-Nate