Update table values from another table with the same user name

Solution 1:

As long as you have suitable indexes in place this should work alright:

UPDATE table_a
SET
      column_a_1 = (SELECT table_b.column_b_1 
                            FROM table_b
                            WHERE table_b.user_name = table_a.user_name )
    , column_a_2 = (SELECT table_b.column_b_2
                            FROM table_b
                            WHERE table_b.user_name = table_a.user_name )
WHERE
    EXISTS (
        SELECT *
        FROM table_b
        WHERE table_b.user_name = table_a.user_name
    )

UPDATE in sqlite3 does not support a FROM clause, which makes this a little more work than in other RDBMS.

If performance is not satisfactory, another option might be to build up new rows for table_a using a select and join with table_a into a temporary table. Then delete the data from table_a and repopulate from the temporary.

Solution 2:

Starting from the sqlite version 3.15 the syntax for UPDATE admits a column-name-list in the SET part so the query can be written as

UPDATE table_a
SET
    (column_a_1, column_a_2) = (SELECT table_b.column_b_1, table_b.column_b_2
                                FROM table_b
                                WHERE table_b.user_name = table_a.user_name )
WHERE
    EXISTS (
       SELECT *
       FROM table_b
       WHERE table_b.user_name = table_a.user_name
   )

which is not only shorter but also faster