How to remove rows from tables selected by select?

Question

MySQL

I need to delete rows from tables, I write a query

DELETE FROM ( SELECT * FROM `oc_product` p LEFT JOIN oc_product_description p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p3s ON (p.product_id = p3s.product_id) LEFT JOIN oc_category_description p4s ON (p3s.category_id = p4s.category_id) LEFT JOIN oc_product_to_store p5s ON (p.product_id = p5s.product_id) LEFT JOIN oc_product_to_layout p6s ON (p.product_id = p6s.product_id) WHERE p.manufacturer_id = '30' )

gives an error message. If it's just select, then everything is selected.

Answer 1

DELETE
FROM
  `oc_product`
WHERE
  id IN (
    SELECT
      p.product_id
    FROM
      `oc_product` p
    LEFT JOIN oc_product_description p2s ON (
      p.product_id = p2s.product_id
    )
    LEFT JOIN oc_product_to_category p3s ON (
      p.product_id = p3s.product_id
    )
    LEFT JOIN oc_category_description p4s ON (
      p3s.category_id = p4s.category_id
    )
    LEFT JOIN oc_product_to_store p5s ON (
      p.product_id = p5s.product_id
    )
    LEFT JOIN oc_product_to_layout p6s ON (
      p.product_id = p6s.product_id
    )
    WHERE
      p.manufacturer_id = '30'
  )

Answer 2

Considering only LEFT JOIN is used
To remove records from related tables, you need to make the relationship foreign keys (FOREIGN KEY) with the attribute ON DELETE CASCADE