Update one column for all rows

Question

The value of column file_path is something like

~/uploaded/de-DE/56/57d8c7d9-dcfc-4208-9f4c-2bf369690bd5.jpeg

I am newbie on MySql and I can't update these values for all rows to something like

~/uploaded/de-DE/56/

This is the Directory only (not the file). I want to remove the name of the file from the string.

What query are you currently using? If you want to update every row simply remove the where clause. Or, is the problem related to stripping off the filename. Your question doesn't make much sense as it stands. — Ben
– Ben, Commented Jun 3, 2012 at 11:10

Brenton Alker · Accepted Answer · 2012-06-03 11:25:55Z

If you know the file name is on all rows, then something like this?

UPDATE table SET file_path = SUBSTR(file_path, 1, LENGTH(file_path) - LOCATE('/', REVERSE(file_path))+1) WHERE 1;

It's not pretty, but it should work.

It would be simpler if MySQL had a find last occurance, but it doesn't as far as I know; hence the LENGTH - LOCATE REVERSE incantation.

(Back up your table first :)

Yes, the WHERE is not required. (Unless "SQL_SAFE_UPDATES" flag is in effect)

Ionel Lupu · Accepted Answer · 2012-06-03 10:13:57Z

0

if you have something like this

~/uploaded/de-DE/'.$folder.'

change it to

~/uploaded/de-DE/'.$folder.'/

I think you missed a backslash

mysql_query("update ".$table." set ".your cell."='".your data."' ");

edited Jun 3, 2012 at 10:13

answered Jun 3, 2012 at 10:08

Ionel Lupu

2,8116 gold badges32 silver badges56 bronze badges

1 Comment

user217648 Over a year ago

thank you for your answer, I have already changed the query but I have to change the column for the exisiting rows. I HAVE TO remove the file name from the end of the string.

Collectives™ on Stack Overflow

Update one column for all rows

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related