sqlite:从联接更新列

我试过下面的答案,但是我找不到一个合适的方法

1. SQLite inner join - update using values from another table
2. How do I make an UPDATE while joining tables on SQLite?
3. Update table values from another table with the same user name

import sqlite3
import pandas

conn = sqlite3.connect('foo.db')
curs = conn.cursor()

df1 = pandas.DataFrame([{'A' : 1, 'B' : 'a', 'C' : None}, {'A' : 1, 'B' : 'b', 'C' : None}, {'A' : 2, 'B' : 'c', 'C' : None}])
df1.to_sql('table1', conn, index = False)

df2 = pandas.DataFrame([{'A' : 1, 'D' : 'x'}, {'A' : 2, 'D' : 'y'}])
df2.to_sql('table2', conn, index = False)

这将产生两个表

pandas.read_sql('select * from table1', conn)
   A  B     C
0  1  a  None
1  1  b  None
2  2  c  None

和

pandas.read_sql('select * from table2', conn)
   A  D
0  1  x
1  2  y

A 和更新列 table1.C 结果 D

这就是我尝试过的

上面列表中的解决方案1

sql = """
    replace into table1 (C)    
    select table2.D
    from table2
    inner join table1 on table1.A = table2.A
"""
curs.executescript(sql)
conn.commit()
pandas.read_sql('select * from table1', conn)
     A     B     C
0  1.0     a  None
1  1.0     b  None
2  2.0     c  None
3  NaN  None     x
4  NaN  None     x
5  NaN  None     y

以上列表中的解决方案2

sql = """
    replace into table1 (C)
    select sel.D from (
    select table2.D as D
    from table2
    inner join table1 on table1.A = table2.A
    ) sel
"""
curs.executescript(sql)
conn.commit()
pandas.read_sql('select * from table1', conn)
     A     B     C
0  1.0     a  None
1  1.0     b  None
2  2.0     c  None
3  NaN  None     x
4  NaN  None     x
5  NaN  None     y

上述列表中的解决方案3

sql = """
    update table1 
    set C = (
    select table2.D
    from table2
    inner join table1 on table1.A = table2.A
    )
"""
curs.executescript(sql)
conn.commit()

pandas.read_sql('select * from table1', conn)
   A  B  C
0  1  a  x
1  1  b  x
2  2  c  x

x ,应该是 y