如何将数据从一个表复制到另一个具有不同结构的表

b91juud3 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(383)

我有一张有这种结构的table

CREATE TABLE `old_reminder` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `type` enum('payment_15_min','payment_1_day','payment_3_day') NOT NULL DEFAULT 'payment_15_min',
  PRIMARY KEY (`id`)
)

我想将它的数据迁移到另一个具有该结构的表中

CREATE TABLE `new_reminders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `payment_reminder_1_count` int(11) DEFAULT NULL,
  `payment_reminder_1_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

基本上每个都有多行 user_id 将转换为1行，所有枚举值都作为列。我尝试了以下操作，但只更新了一行

UPDATE reminders 
    INNER JOIN old_reminder AS `old`
    ON user_id = old.applicant_id
    SET new_reminder_1_date = IF(old.type = 'payment_15_min' OR old.type = 'payment_1_day' OR old.type = 'payment_3_day', old.date, '2018-01-01 00:00:00'),
    payment_reminder_1_count = IF(old.type = 'payment_15_min' OR old.type = 'payment_1_day' OR old.type = 'payment_3_day',
    CASE
    WHEN old.type = 'payment_15_min' THEN 1
    WHEN old.type = 'payment_1_day' THEN payment_reminder_1_count + 1
    WHEN old.type = 'payment_3_day' THEN payment_reminder_1_count + 1 END, 0)
    WHERE applicant_id = 123;

mysql insert-update database-migration

来源：https://stackoverflow.com/questions/48320032/how-to-copy-data-from-1-table-to-another-with-different-structure

1条答案

按热度按时间

cetgtptt1#

如果你的模特长得像这样

truncate table old_reminder;
insert into old_reminder (applicant_id,date,type) values (123,'2018-01-01','payment_15_min');
insert into old_reminder (applicant_id,date,type) values (123,'2018-01-02','payment_1_day');
truncate table new_reminders;
insert into new_reminders(user_id,payment_reminder_1_date) values (123,'2016-01-01');

然后是查询的简化版本

update new_reminders n join (select * from old_reminder order by applicant_id,`date`) old on old.applicant_id = n.user_id
     SET 
     enumstr = old.type,
     payment_reminder_1_date = 
     case 
        when old.type in ('payment_15_min','payment_1_day','payment_3_day') then old.date
        else '2017-01-01'   
     end     
    WHERE applicant_id = 123;

产生这个结果，我想这就是你所说的只更新一行的意思

+----+---------+--------------------------+-------------------------+----------------+
| id | user_id | payment_reminder_1_count | payment_reminder_1_date | enumstr        |
+----+---------+--------------------------+-------------------------+----------------+
|  1 |     123 |                     NULL | 2018-01-01 00:00:00     | payment_15_min |
+----+---------+--------------------------+-------------------------+----------------+
1 row in set (0.00 sec)

where子句在这个查询中是最后一个求值的，并且可以自由地从结果集中选择它想要的结果-在这个例子中总是第一个。
如果查询更改为测试付款提醒日期

update new_reminders n join (select * from old_reminder order by applicant_id,`date`) old on old.applicant_id = n.user_id
     SET 
     enumstr = old.type,
     payment_reminder_1_date = 
     case 
        when old.type in ('payment_15_min','payment_1_day','payment_3_day') then old.date
        else '2017-01-01'   
     end     
    WHERE applicant_id = 123 and n.payment_reminder_1_date <> old.`date`

然后你会得到更像你所希望的东西

+----+---------+--------------------------+-------------------------+---------------+
| id | user_id | payment_reminder_1_count | payment_reminder_1_date | enumstr       |
+----+---------+--------------------------+-------------------------+---------------+
|  1 |     123 |                     NULL | 2018-01-02 00:00:00     | payment_1_day |
+----+---------+--------------------------+-------------------------+---------------+
1 row in set (0.00 sec)

但我不认为这实际上是你正在寻找的，我建议光标可能是前进的方向。

赞(0）回复(0）举报 2021-06-21

我来回答

如何将数据从一个表复制到另一个具有不同结构的表

1条答案

相关问题

热门标签

最新问答