Moving Vocabulary terms to another Vocabulary

My wife was setting up a new drupal [6.9] and had set up her taxonomy so that she had a lot of terms listed under a number of vocabularies. For example, she had a vocabulary where all the nearby local towns were listed as terms. Another would be a vocabulary she called 'products' where individual product types were listed as terms. This was OK as a structure, but the end result was multiple vocabularies with many terms. Being the guy who will maintain the site functionality, I saw this and realized that it was going to be a nightmare updating the vocabularies' accessibility to newly installed modules. I decided that we would be better served having one master global vocabulary, where the current vocabularies were listed as terms, with their current terms as sub terms, thus making accessablity a snap because I would then only have one Vocabulary to change.

I looked for modules that did this but there's nothing out there for 6.x, so I thought I would document the way I went about successfully achieving this by manipulating the database through mySQL.

Basically I needed to use mySQL to change the values in two tables: term_hierarchy and term_data but I needed to take some precautionary measures before starting.

First I needed to figure out the ids of the vocabularies I was moving and the vocabulary I was moving to. The easiest way ot do this is to open the taxonomy section of the admin menu and hold your mouse over the link that is used for editing the vocabulary. look in the status bar of your browser to see the link you are mousing over. The link should end with a number and that number should be that vocabulary's id number. Make a note of the vocabulary ids for the vocabularies you are going to change.

Make a note of that vocabulary id you will be moving terms to [the target vocabulary], or create a new global vocabulary and make a note of it's id.

In the target vocabularies create terms that mirror the names of the existing vocabularies that you want to move. Then make a note of the term ids by mousing over the term name on the term listing page and viewing the status bar which should display the link to the term page view. The number at the end of the link is the term id.

Next make a chart that maps the vocabulary id to the term id so you can keep track of what you are doing. Mine looked something like this:

Vocabulary name | vid | tid
===========================
products | 10 | 246
towns | 12 | 255
etc., etc.

Clear the site Cache - [ Home › Administer › Site configuration > click the 'Clear cached data' button ]. This will avioid any silly cache problems you may encounter moving ahead.

Next put your drupal in maintenance mode [ Home › Administer › Site configuration > Off-line > Save configuration ].

Before proceeding I backed up the entire site including the database so I had a restore point if I screwed up (which I happened to do the first time around). If you don't know how to back up your site and database - I would recommend against proceeding, until you do.

Now you are ready to make your sql changes, so log on to phpMyAdmin, or shell, or Mysql Query Browser.

Before you move your vocabularies, you should first update the parents of the terms in the vocabularies. The Update query used makes changes based on the terms current vocabulary id, so you need to do this firs then 'move' the vocabulary. Our vocabularies were very simple [only one level deep] so the queries we executed reflected that. You may have to make changes to your queries to reflect the intracacies of your existing taxonomy.

To change the vocabulary parents the query we issued was fairly simple:

UPDATE term_hierarchy,term_data SET term_hierarchy.parent=term_id WHERE term_data.tid=term_hierarchy.tid AND term_data.vid=vocab_id;
'term_id' is a number that represents the id of the term you are going to move the vocabulary under in the target vocabulary, and 'vocab_id' is a number that represents the vocaulary id of the vocabulary you are moving terms FROM.

Follow up by changing the vocabulary id, effectively moving the vocabulary:

UPDATE term_data SET term_data.vid=target_vocab_id WHERE term_data.vid=Vocab_id;
target_vocab_id is a number that represents the id of the vocabulary that you are moving terms TO, and 'vocab_id' is a number that represents the vocaulary id of the vocabulary you are moving terms FROM.

Repeat the queries for every vocabulary you are moving.

When you are done with your queries, go to your taxonomy in Admin and make sure that all went well. The terms should be listed in the target vocabulary and the old vocabularies should be empty. Clean up the old vocabularies by deleting them. You are now done and you can bring you site back online.

Depending on your taxonomy, you may have to create or modify the update queries above fit your structure.

I hope somebody else finds this useful and thanks to the Drupal team for suh a kick ass CMS!