0

There are roughly 4000 file paths stored in the img_path column in a table called 'sunshine'.

The structure to all of these paths changed the same way.

I was wondering if it is possible to run a mySQL query that will change all current image path records, to the newly changed one, for all records in the 'sunshine' table?

Note: the only difference between the old and new path is that I need to add a sunny directory to all previous img_paths.

Current 'img_path' column:

images/a/a1.jpg 
images/a/a2.jpg 
images/a/a3.jpg

What I am trying to accomplish:

images/sunny/a/a1.jpg
images/sunny/a/a2.jpg
images/sunny/a/a3.jpg

many thanks in advance!

AnchovyLegend
  • 12,139
  • 38
  • 147
  • 231
  • How about you show us your table schema (`SHOW CREATE sunshine`) and we can help you create a query to update the table? – Kermit Jan 15 '13 at 03:00
  • 1
    see, e.g. http://stackoverflow.com/questions/1755408/mysql-regex-replace - you're looking for REGEXP_REPLACE. – Carl Jan 15 '13 at 03:06

1 Answers1

4

Try this

UPDATE Sunshine
SET img_paths = REPLACE(img_paths,'images/','images/sunny/');
rs.
  • 26,707
  • 12
  • 68
  • 90