将数据从一个表复制到另一个具有特定条件的表 [英] Copy data from one table to another with specific condition

问题描述

我想将数据从一个表复制到另一个表，但只在特定日期之后在列值中处理='1'.

I want to copy data from one table to another but only which has processed='1' in the column value after a specific date.

我有可以执行此操作的代码，但执行时间很长.

I have code which could do it but its taking a long time to execute.

"INSERT INTO eamglo5_billingsystem.`consignment` ( 
`consignment_status`,
  `account`,
  `awb`,
  `hawb`,
  `service`,
  `handling`,
  `reference`,
  `date_submitted`,
  `date_imported`,
  `date_printed`,
  `printed_file_id`,
  `date_received`,
  `date_booked`,
  `booked_file_id`,
  `date_exported`,
  `export_file_id`,
  `company`,
  `contact`,
  `address_line_1`,
  `address_line_2`,
  `address_line_3`,
  `id`
)
     SELECT
        'Y',
       `account`,
      `awb`,
      `hawb`,
      `service`,
      `handling`,
      `reference`,
      `date_submitted`,
      `date_imported`,
      `date_printed`,
      `printed_file_id`,
      `date_received`,
      `date_booked`,
      `booked_file_id`,
      `date_exported`,
      `export_file_id`,
      `company`,
      `contact`,
      `address_line_1`,
      `address_line_2`,
      `address_line_3`,
      `id` 
      FROM  `eamglo5_singaporelive`.`consignment` 
      left join  (
        SELECT eamglo5_billingsystem.`consignment`.`id` as id1 
         FROM eamglo5_billingsystem.`consignment` 
      ) t  ON  `eamglo5_singaporelive`.`consignment`.id >id1
      WHERE `eamglo5_singaporelive`.`consignment`.`processed`=1 
       and `eamglo5_singaporelive`.`consignment`.date_booked>'2018-07-17'

预期:应将 eamglo5_singaporelive.consignment 表中的数据复制到 eamglo5_billingsystem.consignment 表中，其中仅处理=1 个值.

Expected: Should copy data from eamglo5_singaporelive.consignment table into eamglo5_billingsystem.consignment table with only processed=1 values.

实际:花费无限时间来执行和获取行.

Actual: Taking an infinite time to execute and fetch the rows.

推荐答案

您在条件 consignment.id >id1 下的 LEFT JOIN 几乎是在创建 catesian 产品.您可能想要的是仅插入源表中 id 比目标表中最高 id1 更高的行.您应该使用 SELECT MAX(id) 子查询:

Your LEFT JOIN with the condition consignment.id >id1 is almost creating a catesian product. What you probably want, is to insert only rows with a higher id from the source table than the highest id1 in the destination table. You should use a SELECT MAX(id) subquery instead:

SELECT [..]
FROM  `eamglo5_singaporelive`.`consignment` 
WHERE `eamglo5_singaporelive`.`consignment`.`processed`=1 
  and `eamglo5_singaporelive`.`consignment`.date_booked>'2018-07-17'
  and `eamglo5_singaporelive`.`consignment`.id > (
     SELECT MAX(id1) FROM eamglo5_billingsystem.`consignment`
  )

这篇关于将数据从一个表复制到另一个具有特定条件的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！