SQL 从具有相同用户名的另一个表更新表值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3845718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update table values from another table with the same user name
提问by Bin Chen
I have two tables, with a same column named user_name, saying table_a, table_b.
我有两个表，有一个名为 的同一列user_name，上面写着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_nameis the same, how to do it in SQL statement?
我想分别copy from table_b, column_b_1, column_b2, to table_b1, column_a_1, column_a_2, 哪里user_name一样，在SQL语句中怎么做？
回答by martin clayton
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 sqlite3does not support a FROM clause, which makes this a little more work than in other RDBMS.
sqlite3中的UPDATE不支持 FROM 子句，这使得它比其他 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.
如果性能不令人满意，另一种选择可能是使用 select 为 table_a 构建新行并将 table_a 连接到临时表中。然后从 table_a 中删除数据并从临时数据中重新填充。
回答by Alejadro Xalabarder
Starting from the sqlite version 3.15 the syntax for UPDATEadmits a column-name-list in the SET part so the query can be written as
从 sqlite 3.15 版开始，UPDATE的语法在 SET 部分允许列名列表，因此查询可以写为
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
这不仅更短而且更快
回答by TheFlyingDutchMoose
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)
;
