How to delete all rows with type y that also contain an entry in the same table with type x?

user_3181492 0
Viewed 71

What I mean is that I want to remove all rows in a table which are in that same table with another type. I tried to define this in a query but as I am selecting from the table where I also want to delete row from, the query cannot be processed.

Query:

DELETE FROM `user`
WHERE  `user_id` IN (SELECT `user_id`
                     FROM   `user`
                     WHERE  `type` = 'x')
       AND `type` = 'y';

How can I rewrite this query sothat this will work?

mariadb mysql
3 Answers

During an Update clause on a particular table, MySQL does not allow you to use the same table as a "source" for the subquery in the WHERE condition.

However, you don't need to use a subquery here, and a simple "Self-Inner-Join" would suffice.

DELETE u1 FROM `user` AS u1 
JOIN `user` AS u2 
  ON u2.`user` = u1.`user` AND 
     u2.`type` = 'x'
WHERE u1.type = 'y';
user_24693080

just use inside another subquery it will work

DELETE FROM `user`
WHERE  `user_id` IN ( select * from (SELECT `user_id`
                     FROM   `user`
                     WHERE  `type` = 'x') a)
       AND `type` = 'y';
user_68085830

Have you tried: 

DELETE FROM `user`
WHERE  `user_id` IN (SELECT `user_id`
                     FROM   `user`
                      WHERE  `type` = 'x' OR `type` = 'y')

The inner query selects all entries where type is 'x' or 'y', this will delete all usersof type x and y.

Assuming that the shape of the table looks like this:

USER

user_id | type
1          x 
2          x
3          y
4          y
5          z
user_103952090
Related