SQLite inner join - update using values from another table

user_1112613 0
Viewed 27315

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
sql-update join sqlite sql
5 Answers

As Tony says, the solution is the replace into way but you can use the sqlite hidden field rowid to simulate full update with join like:

replace into table2
(rowid,a, b, c, d, e, f, g)
select dest.rowid,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

With this you recreate full rows if you don't have primary key for the replace or as standard method to do the updates with joins.

user_25951890
Related