CODEWITHSUNDEEP
mysqlsqlyiidatabase-migration
Matt
Matt

Reputation: 5567

Yii DbMigration Moving Data to other table

I am writing a CDbMigration in Yii to create a new table with a foreign key to an existing table. That is easy. I also want to take every entry in the old table and copy its id, thumbnail, and myfunction(title) to the new table before dropping thumbnail in the old table.

The old_table before the migration should look like (truncated)

CREATE TABLE IF NOT EXISTS `old_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `thumbnail` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

And after I will have a new_table and the old table without the thumbnail

CREATE TABLE IF NOT EXISTS `new_table` (
  `old_id` int(11) unsigned NOT NULL, -- foreign key to old_table
  `transformed_title` varchar(255) NOT NULL,
  `thumbnail` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Also, myfunctionis a function to transform a string.

Now I have come to understand that using CActiveRecord is bad practice in a migration, but my SQL skills are not sharp enough to know if there is a SQL command to do what I want for the migration. Or is this something in which I should separately copy the data in a script (i.e. not what I should be using a migration for)?

Upvotes: 0

Views: 1310

Answers (1)

Jon
Jon

Reputation: 437376

Generally speaking, you can copy data to another table using INSERT...SELECT; since you ask for an SQL command, this is probably what you are looking for:

INSERT INTO new_table (old_id, transformed_title, thumbnail)
SELECT id, MYFUNCTION(title), thumbnail FROM old_table

However, myfunction needs to be a function that MySql provides otherwise this query (or any other approach in pure SQL) will never be able to work.

Upvotes: 1

Related Questions