Update table values from another table with the same user name

Question

I have two tables, with a same column named user_name, saying table_a, table_b.

I want to, copy from table_b, column_b_1, column_b2, to table_b1, column_a_1, column_a_2, respectively, where the user_name is the same, how to do it in SQL statement?

Answer 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.

Answer 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