mysql DELETE "gets stuck"
Posted on 2014-12-01
I've inherited the following "nested" DELETE query,
where the desire is to delete those records in the Purchase_order_details table that
are referred to in the Sales_order_details table.
po_detail_key <-------------- po_detail_key
so_header_key = 123456 (a specific value when handed to mySQL)
DELETE FROM tblPurchase_order_details
WHERE po_detail_key IN
(SELECT po_detail_key FROM tblSales_order_details
The server shows the mySQL state of this query to be "Sending data"
and it's stuck there until i restart mySQL on the server.
I can unravel the "nested" query to be implemented using a PHP loop, but wondering if there's
a way it can work WITHIN mySQL?
p.s. it's INNODB with no constraints defined, all other queries work fine.
the design, etc. had been translated from an ASP/Microsoft implementation from 15+ yrs ago.