How to update url_alias after SELECT FROM url_alias After a rather clumsy migration by a previous developer, my new client's site has a whole bunch of incorrect paths, which I'd like to update in bulk (nearly 7000 nodes, so I'll not be doing this by hand).

In particular, their forum topics are aliased like "/content/12345" rather than "forum/12345" as desired.

I've pretty much figured out how to do this directly in MySQL, but I've hit a snag - MySQL won't allow me to update a table I've done a select from, at least in this nested query.

Here's my query:

UPDATE url_alias SET alias = REPLACE(alias, 'content/', 'forum/') WHERE alias in (SELECT alias FROM url_alias LEFT JOIN node ON CONCAT('node/',node.nid) = url_alias.source WHERE node.type = 'forum');


and MySQL says:

ERROR 1093 (HY000): You can't specify target table 'url_alias' for update in FROM clause


Perhaps I should give up and do this in two stages in PHP...?
Or is there in fact a way to do this directly in MySQL?
Regarding the query, you can do the query using a temporary table:

CREATE TABLE `tmp` ( alias varchar(255));

INSERT INTO tmp (SELECT alias 
FROM url_alias LEFT JOIN node 
ON CONCAT('node/',node.nid) = url_alias.source 
WHERE node.type = 'forum');

UPDATE url_alias 
SET alias = REPLACE(alias, 'content/', 'forum/') 
WHERE alias in (SELECT alias FROM tmp);

DROP TABLE tmp;


But if there are lots of those aliases with all different cases, it's better to follow the following steps:


Delete all records from the url_alias table.

TRUNCATE TABLE url_alias;
Go to your admin interface at yoursite.com/admin/config/search/path/patterns and define the patterns for the needed node types.
Go to the 'bulk updates' tab of the URL Aliases definitions (yoursite.com/admin/config/search/path/update_bulk) and bulk update all content paths.