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

Re: SQL UPDATE query. 80 80

I hope following will help you !

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

Vivi

Re: SQL UPDATE query. 80 80

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?

Re: SQL UPDATE query. 80 80

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.

Re: SQL UPDATE query. 80 80

Use :

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

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

Re: SQL UPDATE query. 80 80

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

Re: SQL UPDATE query. 80 80

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.

Re: SQL UPDATE query. 80 80

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.

Re: SQL UPDATE query. 80 80

Thanks Tyson

Vivi

Re: SQL UPDATE query. 80 80

Thanks Tyson

Vivi

=D no problem

Re: SQL UPDATE query. 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.