How do I properly construct an UPDATE statement that references an attached database table?
Question: this question pertains to Python3's sqlite3 module (DB-API 2.0 interface for
SQLite databases).

I am trying to update a sqlite3 database column where there are equivalent
values in an attached database table. I fail to understand why my sqlite
statement fails with the error message "no such column". I posted a minimal
example below. My goal is to update the "number" column in table_b for rows
where the "word" column matches the "string" column of table_a from the
attached database "db1".

Any help would be greatly appreciated. TIA for your time.

    
    
    import sqlite3
    conn = sqlite3.connect('db1.dat')
    c = conn.cursor()
    c.execute('''CREATE TABLE table_a (string TEXT)''')
    c.execute('''INSERT INTO table_a VALUES ('Lorem')''')
    c.execute('''INSERT INTO table_a VALUES ('ipsum')''')
    c.execute('''INSERT INTO table_a VALUES ('dolor')''')
    conn.commit()
    c.close
    
    conn = sqlite3.connect('db2.dat')
    c = conn.cursor()
    c.execute('''CREATE TABLE table_b (word TEXT, number INTEGER)''')
    c.execute('''INSERT INTO table_b VALUES ('sit', 0)''')
    c.execute('''INSERT INTO table_b VALUES ('amet', 0)''')
    c.execute('''INSERT INTO table_b VALUES ('dolor', 0)''')
    conn.commit()
    c.execute('''ATTACH 'db1.dat' AS db1''')
    c.execute('''UPDATE table_b SET number = 1 WHERE table_b.word = table_a.string''')
    -----------------------------------------------------------------------------------------
    OperationalError                          Traceback (most recent call last)
    /home/newb/sandbox/<ipython-input-1-3659f61710d7> in <module>()
         17 c.execute('''PRAGMA database_list''')
         18 c.fetchall()
    ---> 19 c.execute('''UPDATE table_b SET number = 1 WHERE table_b.word = table_a.string''')
    
    OperationalError: no such column: table_a.string
    


Answer: Nevermind, I figured it out. The update statement that I was looking for is:

    
    
    c.execute('''UPDATE table_b SET number = 1 WHERE table_b.word IN (SELECT string FROM table_a)''')
    

I found guidance here:
<http://www.tutorialspoint.com/sqlite/sqlite_sub_queries.htm>

