What is the easiest way to do complex schema updates on a production mysql database?

I have a fairly large database that will undergo some massive schema changes. There are many new entities and relationships that will be added. The existing schema, frankly, doesn't have enough information to generate the rows and relationships required to migrate the data using basic UPDATE and INSERT statements.

What is the easiest way to insert and update records in a mysql database that require fairly heavy analysis on the existing data? A programming language? Mysql's programming capabilities?

Ideally, I'd like to run it as a script with the rest of the data migration code (alter tables, create tables, and so on), so it all happens in order and at the same time. But I am not the most experienced at开发者_JAVA百科 writing sql-based programs - like using variables, functions, logic, etc. In my 20 years of writing applications, I just never write stored procedures or sql-based programs - especially these days.

So, I'm probably looking at a day or 2's work just to migrate data if I do that.

Is there another option that will work? Or is getting into the nitty-gritty details of SQL pretty much required?

Any advice?

Here's the basic algorithm of what I need to do:

• find one teacher (at random? the first id?) out of all the teachers to represent each education facility
• find all the allowed modules for each education facility
• create a course for each allowed module for the teacher that represents the education facility
• for each active module (that has a reference to a module an education facility), find the course that was just created above and link it to the active module
• remove the module reference from active module

If you are migrating the data and changing the schema at the same time I would recommend you create a new database (with any changes to the schema you want) then migrate the data to that without modifying the orginal database; which should also be backed up as well, just to be sure.

In such a large update something will go wrong so you always want to be in the position to throw away your new database, fix the translation problem, create a new blank DB and try again.

I realise you are working in MySQL but do you have access to SQL Server as well? If you do you could use SSIS to migrate the data (unless someone else knows of an open source tool similar to SSIS which can be used with MySQL?)

It's an excellent tool and doesn't require loads of SQL language knowledge to get things working although I would recommend you buy a book on the subject. I've never tried to connect SSIS to MySQL but I understand you can using ODBC, I found an explanation here: Connecting to MySQL from SSIS