In my other question I asked how to select a column from multiple tables with inner joins. My new question is: how to delete these results?
SELECT
product_image.image
FROM product
INNER JOIN ixml_prd_map ON product.sku = ixml_prd_map.id_oc_prd
INNER JOIN product_image ON product_image.product_id = product.product_id
WHERE product.model = "xy-type"
If you want to delete only from products then the below should do the job
delete p from product p
INNER JOIN ixml_prd_map ipm ON p.sku = ipm.id_oc_prd
INNER JOIN product_image pi ON pi.product_id = p.product_id
WHERE p.model = "xy-type"
But if you need to delete from all the tables matching the joining condition then use
delete p,ipm,pi from product p
INNER JOIN ixml_prd_map ipm ON p.sku = ipm.id_oc_prd
INNER JOIN product_image pi ON pi.product_id = p.product_id
WHERE p.model = "xy-type"
You can use this query
DELETE FROM product_image WHERE product_image_id IN (SELECT
product_image.image
FROM product
INNER JOIN ixml_prd_map ON product.sku = ixml_prd_map.id_oc_prd
INNER JOIN product_image ON product_image.product_id = product.product_id
WHERE product.model = "xy-type")
EDIT : From the manual
Currently, you cannot delete from a table and select from the same table in a subquery.
If you want to modify the same query you can execute it by creating a temporary table (here its resultset)
DELETE FROM product_image WHERE product_image_id IN ( SELECT resultset.product_image_id FROM (SELECT
product_image.product_image_id
FROM product
INNER JOIN ixml_prd_map ON product.sku = ixml_prd_map.id_oc_prd
INNER JOIN product_image ON product_image.product_id = product.product_id
WHERE product.model = "xy-type") AS resultset )
OR you can use the USING like this in the example from the MySQL Manual,
13.2.2 DELETE Syntax. I haven't used the USING, but you can definetely check out.
DELETE FROM t1, t2 USING t1
INNER JOIN t2
INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
Also this SO post will help too MySQL Error 1093 - Can't specify target table for update in FROM clause
Currently, you cannot delete from a table and select from the same table in a subquery. : From the manual dev.mysql.com/doc/refman/5.0/en/delete.htmlimage for product_image_id