splitted subquery and virtual table

I have 3 tables Tag, Software and Software_Tag which make many-to-many relationship between two first.

I wish to split the subquery out on two parts (because i can not do in in one in mysql):

(SELECT softwares_id FROM Software_Tag GROUP BY softwares_id HAVING COUNT(*) >=2) as my

DELETE FROM Software_Tag WHERE tags_id=1 AND softwares_id IN (my)

Here i try use virtual table named 'my' to put it to second query. How could i do it?

now it gets en exception like check your 开发者_运维问答sql server.

--

You could just do it as:

DELETE FROM Software_Tag WHERE tags_id=1 AND softwares_id IN
(SELECT softwares_id FROM Software_Tag GROUP BY softwares_id HAVING COUNT(*) >=2)

This should work in MySQL.

Edit .. try this

DELETE ST1
FROM Software_Tag ST1 
JOIN (SELECT softwares_id FROM Software_Tag GROUP BY softwares_id HAVING COUNT(*) >=2) ST2 on ST1.softwares_id=ST2.softwares_id
where ST1.tags_id=1

To use temporary tables, you need to do it like this:

DROP TABLE IF EXISTS my_tmp;
CREATE TEMPORARY TABLE my_tmp SELECT softwares_id 
    FROM Software_Tag GROUP BY softwares_id 
    HAVING COUNT(*) >=2;
DELETE FROM Software_Tag 
    WHERE tags_id=1 
        AND softwares_id IN (SELECT softwares_id FROM my_tmp);