BCM BCM - 1 month ago 10
SQL Question

Update Access table with value from another table via INNER JOIN

I'm trying to update a column using pyodbc with data from a column in another table in the same database. I've tried:

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


and get a "missing operator" syntax error.

I've also tried:

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


which also errors out. What's the correct logic here?

Answer

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

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