从另一个具有相同用户名的表中更新表值 [英] Update table values from another table with the same user name

查看:36
本文介绍了从另一个具有相同用户名的表中更新表值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个表,有一个名为 user_name 的列,分别是 table_atable_b.

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

我想,从table_bcolumn_b_1column_b2复制到table_b1column_a_1column_a_2,分别是user_name相同的地方,在SQL语句中怎么做?

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?

推荐答案

只要你有合适的索引,这应该没问题:

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
    )

sqlite3 中的 UPDATE 不支持 FROM 子句,这使得它比在其他 RDBMS 中.

UPDATE in sqlite3 does not support a FROM clause, which makes this a little more work than in other RDBMS.

如果性能不令人满意,另一种选择可能是使用 select 为 table_a 构建新行并与 table_a 连接到临时表中.然后从table_a中删除数据并从临时数据中重新填充.

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.

这篇关于从另一个具有相同用户名的表中更新表值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆