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.
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`
)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments