Android之SQLite——update基于A表更新B表中的值

转载：http://stackoverflow.com/questions/3845718/sql-how-to-update-table-values-from-another-table-with-the-same-user-name

 

文章内容如下所示：

sql: how to 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：

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

 

UPDATE table_a
SET
      table_a.column_a_1 = (SELECT table_b.column_b_1 
                            FROM table_b
                            WHERE table_b.user_name = table_a.user_name )
    , table_a.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.

 

结果：经测试该方法可行。靠谱！赞一个！哈哈！