Changeset 6518

Timestamp:

Apr 11, 2008, 2:11:39 PM (14 years ago)

Author:

toraneko

Message:

#2178 トピックコメント記入履歴時間用のコンバート

File:

• OpenPNE/branches/work/toraneko/prj_dbtuning/setup/sql/mysql41/update/update-for2.12-db-tuning.sql (modified) (1 diff)

OpenPNE/branches/work/toraneko/prj_dbtuning/setup/sql/mysql41/update/update-for2.12-db-tuning.sql

@@ -22 +22 @@
 CREATE INDEX `c_member_id_c_diary_id` ON `c_diary_comment_summary`(c_member_id,c_diary_id);
 
+/* 既存データのコンバート */
+/* コメント記入履歴時間  */
+DROP TABLE IF EXISTS `tmp_c_commu_topic`;
+CREATE TABLE `tmp_c_commu_topic` (
+  `c_commu_topic_id` int(11) NOT NULL default '0',
+  `r_datetime_comment` datetime NOT NULL default '0000-00-00 00:00:00',
+  PRIMARY KEY  (`c_commu_topic_id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+INSERT INTO tmp_c_commu_topic (c_commu_topic_id, r_datetime_comment)
+  (SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime_comment
+     FROM c_commu_topic_comment AS cctc ,c_commu_topic AS cct
+     WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id 
+     GROUP BY cct.c_commu_topic_id);
+
+UPDATE c_commu_topic INNER JOIN tmp_c_commu_topic USING (c_commu_topic_id)
+ SET c_commu_topic.r_datetime_comment = tmp_c_commu_topic.r_datetime_comment;
+
+DROP TABLE tmp_c_commu_topic;
+