SQLite inner join - update using values from another table

Question

This is quite easy and has been asked multiple times but I can't get it to work. The SQL query I think should work is:

    UPDATE table2
       SET dst.a = dst.a + src.a,
           dst.b = dst.b + src.b,
           dst.c = dst.c + src.c,
           dst.d = dst.d + src.d,
           dst.e = dst.e + src.e
      FROM table2 AS dst 
INNER JOIN table1 AS src
        ON dst.f = src.f

Answer 1

Using the update statement it is not possible because in sqlite joins in an update statement are not supported. See docs: update statement

If you only wanted to update a single column to a static value, you could use a subquery in the update statement correctly. See this example: How do I make an UPDATE while joining tables on SQLite?

Now in your example, making an assumption that there is a unique key on "column f" - a workaround/solution I have come up with is using the replace statement:

replace into table2
(a, b, c, d, e, f, g)
select src.a, src.b, src.c, src.d, src.e, dest.f, dest.g
from table1 src
inner join table2 dest on src.f = dest.f

I also added an extra column to table2 "column g" to show how you'd "update" only some of the columns with this method.

One other thing to be cautious about is if you use "PRAGMA foreign_keys = ON;" it's possible to have issues with this as the row is effectively deleted and inserted.