使用相同的用户名从另一个表更新表值

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)


;

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

Update tbl1 Set field1 = values field2 = values Where primary key in tbl1 IN ( select tbl2.primary key in tbl1 From tbl2 Where tbl2.primary key in tbl1 = values);

The accepted answer was very slow for me, which is in contrast to the following:

CREATE TEMPORARY TABLE t1 AS SELECT c_new AS c1, table_a.c2 AS c2 FROM table_b INNER JOIN table_a ON table_b.c=table_a.c1;


CREATE TEMPORARY TABLE t2 AS SELECT t1.c1 AS c1, c_new      AS c2 FROM table_b INNER JOIN t1      ON table_b.c=t1.c2;