Update value based on another value in the same row

I have a database table where I have quite a few old values for filenames - in this case, the filenames on the backend has changed, and I'd like to update the old filenames to the new ones (the actual filenames have been changed, so now the filename in the database doesn't match the one in the filesystem).

What I would like to do is this:
Each row has a unique identifier (foreign key) that is user_id - I would like to update the picture_name-value based on this - currently they can have different values, but the user_id is the distinction.

What I would like to do is something like this:

UPDATE user_pictures SET picture_name = 'userid_'.user_id.'_profile_picture' WHERE picture_name LIKE '%user_%' OR picture_name LIKE '%picture_%'

Code (markup):

of course this doesn't work, but is there a way I can concoct the string with the current row's user_id in it?

 

can you show an example what is you current file name and what it should be

 

The current file names can vary, and aren't really important - they're all gonna be the same in the future, though, just with the distinction of having the user_id in the name (yes, I know I don't need to store the name in the database as long as they're all the same, but I'm chosing to anyway to cater for future updates.
The name I want to have is:
userid_'.$user_id.'_profile_picture
I've based this on a PHP-variable - I know this doesn't work in MySQL (although I could, of course, just make a PHP-script pulling the user_id from the database and update the name based on that.
I was mostly wondering if it's possible to concoct a value based on a value in another column in the same row.
Say my table consist of this:
id, user_id, filename, file_extension
And I want to update filename based on the value in user_id.

 

ok so maybe like this

UPDATE user_pictures SET picture_name = concat('userid_', user_id,'_profile_picture') WHERE picture_name LIKE '%user_%' OR picture_name LIKE '%picture_%'

Code (CSS):

 

Hm. Good thought, but it doesn't seem to work. I've tried just doing this:

UPDATE user_pictures SET picture_name = concat('userid_', user_id,'_profile_picture') WHERE picture_name LIKE '%user_%'

Code (markup):

And it returns 0 rows - same if I add a 'AND user_id = <number>' on the end - so it doesn't actually update anything.
Haven't looked into this extensively, but maybe you have an idea what might be wrong?

 

i would say, if it return 0 rows, than your where clause is not correct.
can you show some example rows from your table

 

id     user_id           picture_name                   picture_name_ext
1         2               user_2_profile                        jpg
2         17             user_17_picture                     png

Code (markup):

For instance - what I would like is to just update all the rows (the picture_name)
So for the ones above it would end up like this

id     user_id           picture_name                   picture_name_ext
1         2               userid_2_profile_picture                        jpg
2         17             userid_17_profile_picture                     png

Code (markup):

And here's the queries I tried:

UPDATE user_pictures SET picture_name = concat('userid_', user_id,'_profile_picture') WHERE picture_name LIKE '%user_%'
UPDATE user_pictures SET picture_name = concat('userid_', user_id,'_profile_picture') WHERE picture_name LIKE '%_picture%'
UPDATE user_pictures SET picture_name = concat('userid_', user_id,'_profile_picture') WHERE user_id = 2 // just to see if the query would pick up at all

Code (markup):

 

i have just tried on http://sqlfiddle.com/ a simple example
and it works fine with id and like filter.

create table test_tab (
  user_id int,
  prodname varchar(100),
  picname varchar(100)
);

insert into test_tab (user_id,prodname,picname) values (1,'prod1','userid_xyz');
insert into test_tab (user_id,prodname,picname) values (2,'prod2','userid_xyz');
insert into test_tab (user_id,prodname,picname) values (3,'prod3','123_picture');
insert into test_tab (user_id,prodname,picname) values (4,'prod4','567_picture');


update test_tab
set picname = concat('userid_',user_id, '_new')
where picname  like '%user_%';

Code (markup):