I have a FEDERATED table from another local database from which I have created a VIEW in MySQL, set up as below:
select `wo_line_items`.`work_order_id` AS `work_order_id`,
sum(`wo_line_items`.`quantity`) AS `SumOfquantity`,
sum(`wo_line_items`.`shipped`) AS `SumOfshipped`,
(sum(`wo_line_items`.`quantity`) - sum(`wo_line_items`.`shipped`)) AS `shipped`
from (`work_orders` join `wo_line_items` on((`work_orders`.`id` = `wo_line_items`.`work_order_id`)))
group by `wo_line_items`.`work_order_id`,`work_orders`.`status` having ((sum(`wo_line_items`.`quantity`) > 0)
and (`work_orders`.`status` = _utf8'inspection complete'))
Using this VIEW I am attempting to run an UPDATE query on the local database as follows:
UPDATE wtbl_stages INNER JOIN view_shipped ON wtbl_stages.wo_id = view_shipped.work_order_id SET
wtbl_stages.stage_date_in = Now(), wtbl_stages.stage_date_out = Now(),
wtbl_stages.stage_action_id = 3, wtbl_stages.stage_notes = 'Auto Closed by Woksu'
WHERE (((wtbl_stages.stage_action_id) is null) AND ((wtbl_stages.process_id)= 21) AND (`view_shipped`.`shipped`)=0);
The query times out after 10 minutes and does not actually appear to run at all.
The view returns a dataset of around 13,000 lines and the udate query is addressing about 19,000 lines.
I am at a loss as to why it hangs. Thanks for reading.