Delete multiple join

Question

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"

score 2 · Answer 1 · answered May 29 '15 at 11:24

2

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"

answered May 29 '15 at 11:24

Abhik Chakraborty

44,654
6
52
63

I just want to delete matched "product_image.image". – Adrian May 29 '15 at 11:34
Yes in the first case the deletion will happen on product table by matching the joining clause and condition and in the 2nd case its deleting from all the tables. You may use which one is convenient for you. – Abhik Chakraborty May 29 '15 at 11:52

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

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

edited May 23 '17 at 11:58

Community

1
1

answered May 29 '15 at 11:16

Nandakumar V

4,317
4
27
47

For some reason I am getting error as above: #1093 - You can't specify target table 'product_image' for update in FROM clause – Adrian May 29 '15 at 11:27
I believe its because In mysql, you cannot update a table and select from the same table in a subquery, `Currently, you cannot delete from a table and select from the same table in a subquery. ` : From the manual https://dev.mysql.com/doc/refman/5.0/en/delete.html – Nandakumar V May 29 '15 at 11:33
Uh-oh. I would delete manually, but there are more than 30 000 result. :( – Adrian May 29 '15 at 11:37
@Adrian you can delete it via query, what I meant was deletion is not possible using the older query, the updated one should work. – Nandakumar V May 29 '15 at 11:43
I am playing with the first version. I created temporary table "resultset" and "product_image_id" column (maybe it's not necessary?). Error: Unknown column 'resultset.product_image_id' in 'field list' http://www.pastebin.ca/3012913 – Adrian May 29 '15 at 15:13
1

Oh sorry that was a mistake. I have updated the query. I mistyped `image` for `product_image_id` – Nandakumar V May 29 '15 at 15:28

Delete multiple join

2 Answers2