How to use sub query in MySQL DELETE statement?
Just simple as bellow:
DELETE FROM clients
WHERE id NOT IN (
SELECT * FROM (
SELECT c.id
FROM products p
INNER JOIN clients c
ON c.id = p.supplier_id
WHERE c.name = 'Q-IMAGING AUSTRALIA LTD (HEAD OFFICE)'
GROUP BY c.id
) client_tbl
)
AND name = 'Q-IMAGING AUSTRALIA LTD (HEAD OFFICE)'
AND type = 'Supplier'