sqlite3.OperationalError: ambiguous column name: views

Help! I try to update the select statement but it seems to not work here So, let me introduced my table Artists and Songs

Note:I already inserted these table yet

c.execute("""create table Artists(
        Artist_id INTEGER primary key,
        artist char(255),
        Most_listened_song char(255));""")



c.execute("""create table Songs(
        Song_ID INTEGER Primary key,
        Song char(255),
        Artist_ID INTEGER,
        FtArtist_ID INTEGER null,
        Genre_ID INTEGER,
        dates Date,
        views INTEGER,
        Foreign key(Artist_ID) references Artists(Artist_ID),
        Foreign key(FtArtist_ID) references Artists(Artist_ID),Foreign Key(Genre_ID) references Genres(Genre_ID));""")

and, this is where I update SELECT statement and cause me an error

    result1='Roar'
    result2='Katy Perry'

    c.execute("""UPDATE Songs Set views = views+1 FROM Songs 
                 Inner join Artists On Artists.Artist_ID = Songs.Artist_ID
                 where Song = ? and Artist = ?""",(result1,result2,))

This is the error

 sqlite3.OperationalError: ambiguous column name: views

So I decided to change from views to Songs.views

result1='Roar'
result2='Katy Perry'

c.execute("""UPDATE Songs Set Songs.views = Songs.views+1 FROM Songs 
             Inner join Artists On Artists.Artist_ID = Songs.Artist_ID
             where Song = ? and Artist = ?""",(result1,result2,))

 

and, it still gave me the error after running

sqlite3.OperationalError: near ".": syntax error

Which one should I edit

Answer

If your version of SQLite is 3.33.0+ then you can use SQlite’s UPDATE-FROM extension to UPDATE statement, with this syntax:

UPDATE Songs 
SET views = views + 1 
FROM Artists 
WHERE Artists.Artist_ID = Songs.Artist_ID 
AND Songs.Song = ? AND Artists.Artist = ?

For previous versions of SQLite you can do it with a subquery:

UPDATE Songs 
SET views = views + 1
WHERE Song = ? 
AND Artist_ID = (SELECT Artist_ID FROM Artists WHERE Artist = ?)