mysql select as var and use in an update-CodePudding

I have a database with these fields:

picture_id  - (varchar) generally the actual filename of the picture (ie. myfile001.jpg)
event - (varchar) string describing the event (ie. Bob's Wedding 2005)
date_of_picture - (varchar) string representing a date (ie. 2004.01.45, Jan 2004, Summer 1969, etc)
filename - (text) - the full path to the picture, including the filename (ie. /pics/bob/2005/wedding)

I'm in the position where I need to change some of the filename entries.

Is there a way I can query on event and use the resulting picture_id's to update the filename's?

Something like:

select picture_id as picid from photos where 
   event='Bob's Wedding 2005' update set filename='/my/new/path/here/$picid'

There could be 100's of picture_id's for any given event.

I'm not a DBA and this is just simple database for my personal use, so please forgive me if my question is unclear or my database structure is completely amateurish.

CodePudding user response：

Translating your select into a proper update, we can try:

UPDATE photos
SET filename = CONCAT('/my/new/path/here/', picture_id)
WHERE event = 'Bob''s Wedding 2005';