Creating variables and reusing within a mysql update query? possible?
Question by miss_piggy
I am struggling with this query and want to know if I am wasting my time and need to write a php script or is something like the following actually possible?
UPDATE my_table
SET @userid = user_id
AND SET filename('http://pathto/newfilename_'@userid'.jpg')
FROM my_table
WHERE filename
LIKE '%_%' AND filename
LIKE '%jpg'AND filename
NOT LIKE 'http%';
Basically I have 700 odd files that need renaming in the database as they do not match the filenames as I am changing system, they are called in the database.
The format is 2_gfhgfhf.jpg which translates to userid_randomjumble.jpg
But not all files in the database are in this format only about 700 out of thousands. So I want to identify names that contain _ but don’t contain http (thats the correct format that I don’t want to touch).
I can do that fine but now comes the tricky bit!!
I want to replace that file name userid_randomjumble.jpg with http://pathto/filename_userid.jpg So I want to set the column user_id in that row to a variable and insert it into my new filename.
The above doesn’t work for obvious reasons but I am not sure if there is a way round what I’m trying to do. I have no idea if it’s possible? Am I wasting my time with this and should I turn to PHP with mysql and stop being lazy? Or is there a way to get this to work?
Answer by Bohemian
Yes you can do it using straightforward SQL:
UPDATE my_table
SET filename = CONCAT('http://pathto/newfilename_', userid, '.jpg')
WHERE filename LIKE '%_%jpg'
AND filename NOT LIKE 'http%';
Notes:
- No need for variables. Any columns of rows being updated may be referenced
- In mysql, use
CONCAT()to add text values together - With
LIKE, an underscore (_) has a special meaning – it means “any single character”. If you want to match a literal underscore, you must escape it with a backslash () - Your two
LIKEpredicates may be safely merged into one for a simpler query
Answer by Starx
Yes it is possible without the php. Here is a simple example
SET @a:=0;
SELECT * FROM table WHERE field_name = @a;
