mySQL: How to Delete in WHERE IN clause

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

So, why do you have more than one value being selected in the subselect? — N.B., Sep 16 '16 at 21:51
it's to demonstrate what i did so far. I don't no how to return 1 value (parent and children) — Marco, Sep 16 '16 at 22:05

score 4 · Accepted Answer · answered Sep 16 '16 at 22:08

4

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

answered Sep 16 '16 at 22:08

Bill Karwin

538,548
86
673
828

Wow! just like that! Thanks – Marco Sep 16 '16 at 22:11

score 1 · Answer 2 · answered Sep 16 '16 at 21:54

1

did you try this?

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
    FROM album a
    LEFT JOIN album a2 ON a2.parent_album_id = a.album_id
    WHERE a.album_id = 76
)
or  a.album_id IN (
    SELECT a2.album_id child
    FROM album a
    LEFT JOIN album a2 ON a2.parent_album_id = a.album_id
    WHERE a.album_id = 76
)

answered Sep 16 '16 at 21:54

clairerb6

99
8

this solution also works, thanks again – Marco Sep 16 '16 at 22:11

mySQL: How to Delete in WHERE IN clause

2 Answers2

Related