Search code examples
mysqlsqljoinsql-delete

Deleting row from mql table using inner join

I'm currently trying to delete a movie from my table that has a score less than -4 or lower and I'm suppose to use a "delete from" and "inner join" from MySQL. But I'm getting a syntax error that I've looked up on multiple different threads but none seem to fix it. Here's my tables

       +-------------------+
       | Tables_in_lab_4_8 |
       +-------------------+
       | movies            |
       | reviews           |
       +-------------------+

Here is my reviews table. You will not need the movies table.

    +-----------+----------+-------+---------------------+
    | review_id | movie_id | score | comments            |
    +-----------+----------+-------+---------------------+
    |         1 |        2 |     5 | god-like            |
    |         2 |        3 |    -5 | Everyone died, why? |
    |         3 |        4 |     2 | Meh                 |
    |         4 |        5 |     6 | Greak kids movie    |
    |         5 |        2 |     3 | Could be better     |
    +-----------+----------+-------+---------------------+

So obviously I want to delete movie_id 3 because it has a lower score than -4. Here is what I tried to type and the syntax error that goes along with it. Any suggestions on what I'm suppose to do? 

    update movies join reviews on reviews.movie_id = movies.movie_id delete review_id from review_id inner join movie_id where score <= -4;

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'delete review_id from review_id inner join movie_id where score <= -4' at line 1
Solution

  • If you want to delete movies that have at least one review with a score lower than -4, you can use exists and a correlated subquery:

    delete from movies
where exists (
    select 1 
    from reviews r 
    where r.movie_id = movie.movie_id and r.score < -4
)

    Note that this would actually leave orphan rows in the reviews table; you should ensure that you have a foreign key with delete cascade enabled to avoid that.