mysql传输查询

Question

我更新了程序处理罚款和费用的方式，现在我需要将旧数据传输/迁移为新格式。它过去的工作方式是将罚款/收费存储在用户记录的字段中。现在有一个全新的表用于跟踪每笔费用。我只想将该费用字段和 1 条记录添加到包含该费用的计费表中。

User table: id as INT, name as VARCHAR, charges as DECIMAL(10, 2)
Charge Table: id as INT, user_id as INT, amount as DOUBLE, type as INT, date_applied as DATETIME, description as VARCHAR

假设我有一条用户记录:(id => 1, name => 'john doe', rates => 4.20) 并希望将其转移到收费记录

(id => 1, user_id => 1, amount => 4.20, type => 1, date_applied => (whatever  time the transfer is taking place), description => 'Initial balance')

如何在 SQL 查询中执行此一次性传输？我不想为这一次交易编写 PHP 脚本。

Answer 1

这应该可以满足您的要求。

INSERT INTO `Charge Table` SELECT NULL, id, charges, 1, NOW(), 'Initial balance' FROM `User Table`