Copy data from one table to another with specific condition

Question

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'

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.

Please explain the condition you are trying to implement. Nothing in your question explains why you have a `LEFT JOIN`. — Gordon Linoff, Jul 21 '19 at 12:04
Possible duplicate of [MySQL: Creating a new table with information from a query](https://stackoverflow.com/questions/6595252/mysql-creating-a-new-table-with-information-from-a-query) — lurker, Jul 21 '19 at 12:04
The condition is `eamglo5_singpaorelive.consignment`.processed='1' and date_booked>'2019-07-19'. Once working i will execute this query to insert rows into eamglo5_billingsystem every 10 minutes or so. — user3855851, Jul 21 '19 at 12:11
the issue is that the select is taking an infinite amount of time to execute. — user3855851, Jul 21 '19 at 12:14

score 0 · Accepted Answer · answered Jul 21 '19 at 12:27

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`
  )

Copy data from one table to another with specific condition

1 Answers1

Linked