ios - 使用连接语句更新数据库时 FMDB 语法错误

Question

我正在尝试更新以下数据库中的列；

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));

我在 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];
        }];

错误:

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

我在这里错过了什么？

Answer 1

SQLite 不允许在 UPDATE 语句中直接连接。

你必须使用子查询:

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