sqlite: update column from join

Question

I've tried with the answers below, but I cannot find a proper way of doing this

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

Here's the problem. I have a sqlite db with two tables. In the code below I use pandas to insert some dummy values

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)

This results in two tables

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

and

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

Now I want to join these two tables on column A and update columns table1.C with the resulting D

This is what I've tried

Solution 1 in the list above

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

wrong

Solution 2 in the list above

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

Solution 3 in the list above

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

which is clearly wrong since the last row is updated to x, it should be y

At this point I'm pretty much out of options. Any help is much appreciated

Answer 1

I think the correct way would be to use the UPDATE-FROM syntax introduced by sqlite version 3.33 (was not available at the time this question was asked):

UPDATE table1 AS dst
    SET C=src.D
    FROM table2 AS src
    WHERE dst.A=src.A

Answer 2

I cannot quite remember (or explain intelligently) why solution 3 doesn't work but I think it's something like table1 in the subquery is not the "same" table1 being updated. (I have struggled with this in the past, and have developed habits to avoid it, no longer remember the reason).

I do know if you change
inner join table1 on table1.A = table2.A to where table1.A = table2.A it will work.

This will also work:

update table1 
    set C = (
    select table2.D
    from table2
    inner join table1 t1 on t1.A = table2.A
    and t1.A = table1.A
    )

Both of these solutions will set C to null if there is no matching row in table2.