CODEWITHSUNDEEP
mysqlsqloracle-database
Uylenburgh
Uylenburgh

Reputation: 1307

Delete multiple entries in SQL database table. Right query?

I have the following table:

-----------------
| Movie | Genre |
-----------------
| A     | Horror |
-----------------
| A     | Thiller |
-----------------
| B     | Comedy |
-----------------
| C     | Drama |
-----------------
| D     | Horror |
 ....

I want to delete Movies that are Horror. But I know that if I simply say 

delete * where genre='Horror'

that will be a wrong query because 1 movie remains - A - because it is also a Thiller. 

| A     | Thiller |

but I want to get rid of A.

What query should I write? I am new to SQL and relational db theory but I guess that maybe we should first take cross product or join or smth of that table.

DBMS: MySQL or Oracle.

Upvotes: 3

Views: 1534

Answers (2)

newtover
newtover

Reputation: 32084

MySQL has a limitation in DELETE that jou can not join or subquery the same table you are deleting from. But if you wrap the required ids in another subquery, it will work:

DELETE FROM m 
USING movies m 
JOIN (
  SELECT Movie
  FROM movies
  WHERE Genre = 'Horror'
) m2:

Upvotes: 0

user330315
user330315

Reputation: 

delete from movies
where movie in (select movie
                from movies
                where genre = 'Horror');

The above query works for Oracle (and Postgres and SQL Server and DB2 and Firebird and H2 and HSQLDB and ...) but not for MySQL.

For MySQL it would need to be rewritten as a join:

delete movies
from movies 
  join movies m2 on movies.movie = m2.movie
where m2.genre = 'Horror';

Upvotes: 2

Related Questions