Update table values from another table with the same user name

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 )

which is not only shorter but also faster

the last "WHERE EXISTS" part

WHERE
    EXISTS (
       SELECT *
       FROM table_b
       WHERE table_b.user_name = table_a.user_name
   )

is actually not necessary

It could be achieved using UPDATE FROM syntax:

UPDATE table_a
SET column_a_1 = table_b.column_b_1
   ,column_a_2 = table_b.column_b_2
FROM table_b
WHERE table_b.user_name = table_a.user_name;

Alternatively:

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

UPDATE FROM - SQLite version 3.33.0

The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows

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 did not support a FROM clause for a long time, which made this a little more work than in other RDBMS. UPDATE FROM was implemented in SQLite 3.33 however (2020-08-14) as mentioned at: https://stackoverflow.com/a/63079219/895245

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.

There is an even much better solution to update one table from another table:

;WITH a AS
(
    SELECT
        song_id,
        artist_id
    FROM
        online_performance
)
UPDATE record_performance
SET
    op_song_id=(SELECT song_id FROM a),
    op_artist_id=(SELECT artist_id FROM a)

;