update multiple rows from different table sqlite3

Question

I want to update rows of table from another table based on a common key using sqlite3. Here are my tables.

table1 structure.

id name common_key
1       10
2       20
3       30

table2 structure.

id  name common_key
11   a    30
12   b    10
13   c    20

After updating table1 should be like below.

id  name common_key
1   b    10
2   c    20
3   a    30

I want to write a single query to update name of table1 from table2 where common_key's match.

Thank you for your time.

Answer 1

We can't use JOIN with UPDATE in sqlite like mysql and sql-sever etc..
But we can achieve it with by using sub-query.

Query

update table1
set name = (
    select name from table2
    where common_key = table1.common_key
);

Example