mySQL update table add string to column

Question

I have a table in mySQL and want to update a specific column holding a directory. The directory structure should be extended and the corresponding columns should be updated with a subdirectory. There are many rows to update so I look for a convenient script to do.

Example:

column c1 holds directory

/startpoint/dir_a/
/startpoint/dir_a/subdir_1/
/startpoint/dir_a/subdir_1/subsubdir_1/
/startpoint/dir_a/subdir_1/subsubdir_2/
/startpoint/dir_a/subdir_1/subsubdir_3/
/startpoint/dir_a/subdir_2/
/startpoint/dir_a/subdir_3/

So I need to move directories followed from startpoint to startpoint/system.

This should be in after script:

/startpoint/system/dir_a/
/startpoint/system/dir_a/subdir_1/
/startpoint/system/dir_a/subdir_1/subsubdir_1/
/startpoint/system/dir_a/subdir_1/subsubdir_2/
/startpoint/system/dir_a/subdir_1/subsubdir_3/
/startpoint/system/dir_a/subdir_2/
/startpoint/system/dir_a/subdir_3/

My (maybe silly) update didn't work as expected:

update table1 set dir=/startpoint/system/||substr(dir,12) where dir like '/startpoint/dir_a/%'

So any idea from expert to do this with a more specific sql update ?

Thanks.

Correct your UPDATE query... quote should be around the value of dir — codingbiz
– codingbiz, Commented Mar 26, 2013 at 21:34

Vatev · Accepted Answer · 2013-03-26 21:45:30Z

1

You can use

UPDATE table SET dir = REPLACE(dir,'/startpoint/dir_a/','/startpoint/system/dir_a/'
WHERE dir LIKE '/startpoint/dir_a/%'

but be careful because it will replace all occurrences of '/startpoint/dir_a/' - if your path is something like '/startpoint/dir_a/random_dir/startpoint/dir_a/' it will mess it up.

answered Mar 26, 2013 at 21:45

Vatev

7,5801 gold badge36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2213429 Over a year ago

Thanks ! Great and works really perfect. So as I define directory from startpoint there will be no miss with double named directories as subdirectories.

Collectives™ on Stack Overflow

mySQL update table add string to column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related