python - Sqlite3 - UPDATE Column by Lookup

Question

I am trying to lookup and update Market_Sector column in Accounts from MarketSector in Clients. The key columns are Client in Accounts Table and Id in Clients in Table. I have written the below code, but the look up only returns None! Please help!

import sqlite3
con = sqlite3.connect('''C:\\Users\\Bank DB1.db''')
cur  = con.cursor()
cur.execute('''ALTER TABLE Accounts ADD Market_Sector''')
cur.execute('''UPDATE Accounts
            SET Market_Sector = (SELECT MarketSector FROM Clients
            WHERE Client = (SELECT Id FROM Clients))''')
con.commit()
con = sqlite3.connect('''C:\\Users\\Bank DB1.db''')
cur.execute('''SELECT * FROM Accounts''')
results = cur.fetchall()
print(results)
con.close()

标签： pythonsqlite

Answer 1

Use aliases for the tables so that the subquery is properly correlated:

UPDATE Accounts AS a
SET Market_Sector = (
  SELECT c.MarketSector 
  FROM Clients AS c
  WHERE c.Id = a.Client
)

Or with proper use of the table names:

UPDATE Accounts
SET Market_Sector = (
  SELECT Clients.MarketSector 
  FROM Clients
  WHERE Clients.Id = Accounts.Client
)