Trouble Created Ddl To Migrate Schema

[jeffContext]

I'm working on a web app that's currently in beta. I need to modify the schema relatively often and so I've recently purchased SQLyog Enterprise to create ddl files to update the database.

I'm having some trouble with what I think is a typical task, so hopefully someone here can help.

A simple analogy to explain my current situation would be a Person table containing the column ADDRESS_FK. Instead of storing one address per person, say the schema now needs to support a 1:* relationship, where the Address table gets a PERSON_FK column.

The Structure Sync Tool produces sql that would get the structure right, but it ignores the existing data. For example, it wants to drop `ADDRESS_FK` and its associated key, along with all the data in that column.

How can I get SQLyog to generate a sql script that would migrate the existing data to the new structure before dropping the columns?

I'd like it to:

• alter Address: insert PERSON_FK and associated keys
• for each person, select the associated Address in address_fk, and insert that value into address.person_fk
• drop the old stuff

Curious to know how other folks are handling this type of issue.

[peterlaursen]

yes .. we should consider ALTER TABLE and not just DROP-CREATE with structure sync!

[jeffContext]

Oh, I guess this is a feature request then… I thought SQLyog already did this, with screens that help you migrate the existing data to where it belongs in the new schema. Something like:

update address a set PERSON_FK=(select id from person where address_fk=a.id);

[Author]

Posts