Update Access table with value from another table via INNER JOIN
<p>I'm trying to update a column using pyodbc with data from a column in another table in the same database. I've tried:</p>

<pre><code>cursor.execute('''
            UPDATE Prospect_SC_Fin_102016
            SET Prospect_SC_Fin_102016.Sym_Ky=Symbol_Ref_102016.Sym_Ky
            FROM Prospect_SC_Fin_102016
            INNER JOIN Symbol_Ref_102016
            ON Prospect_SC_Fin_102016.Symbol=Symbol_Ref_102016.Symbol;
            ''')
con.commit()
cursor.close()
con.close()
</code></pre>

<p>and get a "missing operator" syntax error.</p>

<p>I've also tried:</p>

<pre><code>cursor.execute('''
            UPDATE Prospect_SC_Fin_102016
            SET Prospect_SC_Fin_102016.Sym_Ky=(SELECT Sym_Ky
            FROM Symbol_Ref_102016 WHERE Symbol IN
            (SELECT Symbol FROM Prospect_SC_Fin_102016));
            ''')
con.commit()
cursor.close()
con.close()
</code></pre>

<p>which also errors out. What's the correct logic here?</p>

<p>For an Access database you would want to use a query of the following form:</p>

<pre class="lang-sql prettyprint-override"><code>UPDATE Prospect_SC_Fin_102016
INNER JOIN Symbol_Ref_102016
    ON Prospect_SC_Fin_102016.Symbol=Symbol_Ref_102016.Symbol
SET Prospect_SC_Fin_102016.Sym_Ky=Symbol_Ref_102016.Sym_Ky
</code></pre>

python sql ms-access pyodbc