Marco Marco - 2 months ago 7
MySQL Question

mySQL: How to Delete in WHERE IN clause

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

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