0

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?

  • 1
    If you are using the Pathauto module, you should be able to go in, delete the aliases, put in the correct alias patterns, and then use the bulk alias feature to rebuild them. Otherwise, I'm afraid this is a sql question and will most probably be closed. – Jimajamma Feb 4 '14 at 6:06
  • I completely agree with what Jimajamma said. You should not directly update the database entries unless you are absolutely sure of covering all the dependencies. @Jimajamma You should consider adding your comment as an answer, and it is the recommended way of updating the alliases. – AjitS Feb 4 '14 at 6:08
  • @Jimajamma how to rebuild the existing paths, I can not find easily, I found only the setting for "Bulk generate aliases for nodes that are not aliased" – Bala Feb 4 '14 at 7:13
  • maybe duplicate of drupal.stackexchange.com/questions/64985/… – Bala Feb 4 '14 at 7:16
  • Thanks for the info, guys. Jimajamma, I may in fact go this route. I've checkmarked Druvision's answer, as it does address my original question as to how to do this in MySQL; but perhaps as you say this whole thing belongs on some other board. Very grateful to you all. – Sam Moore Feb 4 '14 at 15:59
2

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:

  1. Delete all records from the url_alias table.

    TRUNCATE TABLE url_alias;

  2. Go to your admin interface at yoursite.com/admin/config/search/path/patterns and define the patterns for the needed node types.

  3. 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.