CODEWITHSUNDEEP
sqlsqlite
Rob
Rob

Reputation: 1280

Updating sqlite database using joins and inner selects

Have been working on this question for a couple hours and have come close but still can't get the results I am wanting. Here is the question below:

For all movies that have an average rating of 4 stars or higher, add 25 to the release year. (Update the existing Rows; don't insert new Rows.) .

The tables required for this query are in another question I have already posted on here, at How to get results of this particular "query". The table names are "movie" and "rating". You can see some sql code there as well if it will help.

The first step I took to solve this problem was to (obviously) look for the movies that have an average rating of 4 stars or higher. I did that with the following query:

SELECT movie.mid, aveMovieRating 
FROM movie JOIN(
    SELECT DISTINCT(rating.mid), 
    COUNT(mid) AS "Number of movies", 
    SUM(stars) AS "Total no# of stars", 
    (SUM(stars)*1.0/COUNT(mid)) AS aveMovieRating
    FROM rating 
    GROUP BY mid
) AS aveRating
ON movie.mid = aveRating.mid
WHERE aveMovieRating >= 4

Then I came to the tricky part - trying to alter the release year, as per question requirements... This is the query I came up with for attempting that:

UPDATE movie
SET year = (year + 25)
WHERE movie.mid IN(
SELECT movie.mid, aveMovieRating 
FROM movie JOIN(
    SELECT DISTINCT(rating.mid), 
    COUNT(mid) AS "Number of movies", 
    SUM(stars) AS "Total no# of stars", 
    (SUM(stars)*1.0/COUNT(mid)) AS aveMovieRating
    FROM rating 
    GROUP BY mid
) AS aveRating
ON movie.mid = aveRating.mid
WHERE aveMovieRating >= 4)

Executing the above query generates the following SQL error in SQLite:

Error: only a single result allowed for a SELECT that is part of an expression

This was my first attempt at a "complicated" question without getting outside help, but this error threw me off. If someone is able to help me alter the above query, and give some pointers about this type of problem, that would be awesome :).

Upvotes: 2

Views: 667

Answers (1)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Remove aveMovieRating from select list. The error says that clearly: "only a SINGLE RESULT allowed for...". IN operator works on a set of single column rows.

UPDATE:

As for update, you can compact it a bit by removing unnecessary columns:

UPDATE movie
SET year = (year + 25)
WHERE movie.mid IN (
        SELECT rating.mid
        FROM rating 
        GROUP BY mid
        HAVING avg(Stars) >= 4
)

Upvotes: 5

Related Questions