FMDB syntax error when updating database with join statement

Question

I am trying to update a column in following db;

create table movies(id integer primary key AUTOINCREMENT, imdb_id varchar(60) UNIQUE, title varchar(100), plot varchar(1000),poster varchar(1000),rating real, predicted_rating real, year integer, is_watched integer);

create table genres(id integer primary key AUTOINCREMENT, genre varchar(60) UNIQUE);

create table movie_genres(id integer primary key AUTOINCREMENT, genres_id integer , movies_id integer, FOREIGN KEY(movies_id) REFERENCES movies(id),FOREIGN KEY(genres_id) REFERENCES genres(id));

my code in IOS

[_writeQueue addOperationWithBlock:^{
            [_writeQueueLock lock];
            [_queue inDatabase:^(FMDatabase *database) {
               BOOL success =  [database executeUpdate:@"UPDATE movies SET m.predicted_rating = m.predicted_rating + 0.05 from movies m left join movie_genres g on m.id = g.movies_id where g.genres_id = ? ;",[NSNumber numberWithInt:genreId], nil];
                if (success==YES) {
                    NSLog(@"prediction calculated");
                }
                else{
                    NSLog(@"prediction failed");
                }
            }];
            [_writeQueueLock unlock];
        }];

error:

prediction failed
DB Error: 1 "near ".": syntax error"

What am I missing here?

Answer 1

SQLite does not allow joins directly in UPDATE statements.

You have to use a subquery:

UPDATE movies
SET predicted_rating = predicted_rating + 0.5
WHERE id IN (SELECT movies_id
             FROM movie_genres
             WHERE genres_id = ?)