When we need to make significant interdependent changes to content on a live Joomla site, working on a copy is the only safe option. If you do not use a copy then the work in progress or any mistakes could break the live site.
You can create a 'throw away' copy of a joomla database and site very quickly by copying the site files to a subfolder (password protect it if you don't want anyone to see your work in progress), copying the database and changing the configuration.php file in the copied site to point at the database copy. You will also need to add the user specified in the configuration file to the database copy. I'll assume you've named your temporary database development_db below.
Creating the copy could take you fifteen minutes or so but the good news here is that you will be able to apply all of your changes in one go when you are happy with them and, if you screw things up while making your changes, you have not created a problem with the live site. You can:
As far as I know, there is no good equivalent to subversion that works for a mysql database so if you are nervous about breaking development you need to back up the database regularly as you go.
Adding a new row in the live_db will use the same id as a new row added in the development_db which could lead to the wrong content being overwritten on the update. Its generally easiest to create 'stubs' (unpublished placeholder articles) in the live_db that will be updated using the above technique when you've set them up and published them for review in the development site. This prevents any risk of you overwriting articles other people write when you copy your work back into the live database (we'll call that live_db).
Of course, if nobody else is touching the content of the site while you are working then you can relax. Just get the new articles working to your satisfaction in development and copy the entire jos_content table over to the live database when you are happy with the changes.
The changed articles need to be selectable in some way. At worst this could be achieved by listing the affected article ids in which case you can use WHERE id IN (123, 124,...) in the sql below. In this example, I'll assume that all the changes are being made to articles in a particular category (id = 60 for this example).
We often forget that SQL is quite happy to deal with multiple databases. Once I'm happy with the development site, I can quickly copy the changes to the live site:
REPLACE INTO `live_db`.jos_content (SELECT * FROM `development_db`.jos_content WHERE catid = 60);
This assumes that you are logged in to mysql with a user who can access both databases with update privilege which should usually be the case.
This technique can be extended to making clean ups of article sections and categories by applying similar changes to the jos_sections and jos_categories tables but care is always needed to deal with the changes that might be being made in the live database particularly in terms of the addition of new rows.
To further reduce the risk, you really need to test the update as well. This is where we introduce the notion of a third 'copy' of the site called staging. When you think you are ready to deploy the changes made in the development site, create a new copy of the live site called staging and deploy your changes to that. Test staging so that you can be confident that both your changes and the deployment of your changes work before you apply them to the live site.
Australia: 07 3103 2894
International: +61 410 545 357