Update query with joins

I had comfortable assumed that we cannot use joins in MySQL along with the update query. But recently I realized that we can use joins with UPDATE query.  Not only does it make your query simple and meaningful but it also saves you the pain of understanding what the ERROR 1093 (HY000): You can't specify target table 'files' for update in FROM clause means.

You would get the above error if you tried to run the query

UPDATE files f set f.filename = REPLACE(filename,'jpg','jpeg') 
WHERE f.fid IN 
 (SELECT f.fid from files f 
    JOIN content_type_test ctt 
      ON ctt.field_test_fid = f.fid 
    JOIN node n 
      ON (ctt.nid = n.nid and n.type ='content_type_test')
  );

Instead reframe the query as

UPDATE files f 
JOIN content_type_test ctt 
  ON ctt.field_test_fid = f.fid 
JOIN node n 
  ON (ctt.nid = n.nid AND and n.type = 'content_type_test')
SET filename = REPLACE(filename,'jpg','jpeg');