I have a project management table with 1000 tasks/records, each task/record has a dynamic starting_date
and ending_date
columns. The values for these dates are obtained by taking a static project "go live" date (e.g. 2022-10-01
) and then subtracting each record's task_offset_days
value (e.g. -211) , thereby getting the value of starting_date
. The end date is calculated from a days_to_complete
column which has a value of for example 60
days, if you add that to the starting_date
you get the final ending_date
.
Example: Online date = 2022-10-01
, offset -211
days, get starting_date = '2022-03-04'
, and then Adding the value 60
of days_offset
, we get ending_date = 2022-05-03
.
The question here is:
I want to copy these 1000 records to another table and in the process update the values of starting_date
and ending_date
based on another go-live date. Considering I have 1000 records to copy and edit, how can I do this in the most efficient way?
Current replication query:
@live_date = '2022-10-01'; // 上线日期 INSERT INTO `new_task_table` ( `property_id`, // 唯一标识符,在原始表中不存在 `status`, `task_desc`, `phase`, `workstream`, `task_level`, `task_owner_group`, `start_date`, // = 上线日期 - 偏移量 `end_date`, // = 开始日期 + 完成天数 `offset_days`, // 例如 -211(注意:可以低于/高于零:-211或120) `days_to_complete`, // 例如 60 `created_at` ) SELECT '31', `status`, `task_desc`, `phase`, `workstream`, `task_level`, `task_owner_group`, `start_date`, `end_date`, `offset_days`, `days_to_complete`, '2022-01-01 00:00:00' // 查询执行的日期 FROM `old_task_table`;
P粉9094764572023-09-08 12:04:17
This may be simpler than you think. You just pass the value to SELECT
@live_date = '2022-10-01'; // 上线日期 INSERT INTO `new_task_table` ( `property_id`, // 唯一标识符,在原始表中不存在 `status`, `task_desc`, `phase`, `workstream`, `task_level`, `task_owner_group`, `start_date`, // = 上线日期 - 偏移量 `end_date`, // = 开始日期 + 完成天数 `offset_days`, // 例如 -211(注意:可以是负数或正数:-211 或 120) `days_to_complete`, // 例如 60 `created_at` ) SELECT '31', `status`, `task_desc`, `phase`, `workstream`, `task_level`, `task_owner_group`, DATE_ADD(@live_date, INTERVAL offset_days DAY) AS start_date, DATE_ADD(@live_date,INTERVAL offset_days + days_to_complete DAY) AS end_date, `offset_days`, `days_to_complete`, '2022-01-01 00:00:00' // 查询执行日期 FROM `old_task_table`;