mySQL: How to Delete in WHERE IN clause [duplicate]

Question

i have this query

SELECT a.album_id parent, a2.album_id child
FROM album a
LEFT JOIN album a2 ON a2.parent_album_id = a.album_id
WHERE a.album_id = 76

Basically, it returns parent and children values, like so:

parent      child
--------------------
76          64
76          106

I want to use those values, the parent and all of the children (76, 64, 106), to delete from another table like so

DELETE a, ai
FROM album a
LEFT JOIN album_image ai ON ai.album_id = a.album_id
WHERE a.album_id IN (76,64,106)

Executing the above query, works perfectly! But, i need the query to be dynamic. I've try this with no success:

DELETE a, ai
FROM album a
LEFT JOIN album_image ai ON ai.album_id = a.album_id
WHERE a.album_id IN (
    SELECT a.album_id parent, a2.album_id child
    FROM album a
    LEFT JOIN album a2 ON a2.parent_album_id = a.album_id
    WHERE a.album_id = 76
)

I get it why it won't work, because the subquery in the where clause returns 2 values. And so i've try CONCAT_GROUP and CONCAT_WS with no success!

What is my solution? Thanks

Answer 1

MySQL is generally unhappy if you try to SELECT and DELETE from the same table in the same query -- even if the SELECT is in a subquery.

But you can do more joins in your DELETE query.

DELETE a, ai
FROM album a
LEFT JOIN album_image ai ON ai.album_id = a.album_id
LEFT JOIN album p ON a.parent_album_id = p.album_id
WHERE a.album_id = 76 OR p.album_id = 76