April 12, 2012

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 LIKE predicates 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;
...

Please fill the form - I will response as fast as I can!