SQL:通过将两个列映射到彼此来更新表。

I have the following two tables:

我有以下两张表:

        Table A
+-------------------+
|___User___|__Value_|
|    3     |  a     |
|    4     |  b     |
|    5     |  c     |
|____6_____|__d_____|



        Table B
+-------------------+
|___User___|__Value_|
|    1     |        |
|    4     |        |
|    5     |        |
|____9_____|________|

My job is to take user from Table A (and their correspondings value) and then map it to Table B and insert those values in there. So from the above example Table B should look like this after running the script:

我的工作是从表A(及其对应值)获取用户，然后将其映射到表B并将这些值插入其中。因此，从上面的例子来看，运行脚本后，表B应该是这样的:

        Table B
+-------------------+
|___User___|__Value_|
|    1     |        |
|    4     |  b     |
|    5     |  c     |
|____9_____|________|

My question is how can I construct an SQL query that will do this for me in an efficient way, if Table A contains 300,000 + entries and Table B contains 70,000 entries?

我的问题是，如果表A包含30万多个条目，而表B包含7万个条目，那么我如何构建一个SQL查询，以一种有效的方式实现这一点?

NOTES: In Table A the User field is not unique and neither is the Value field. However in Table B, both the User and Value fields are unique and should not appear more than once. Neither are primary keys for either tables.

注意:在表A中，用户字段不是唯一的，值字段也不是唯一的。然而，在表B中，用户和值字段都是惟一的，不应该出现多次。两个表的主键都不是。

3 个解决方案

#1

Could be this

可能是这

update table_b as b 
inner join table_a as a on a.User = b.User
set b.value = a.value

#2

In real-world situations, it would be more likely that you want a predictable value, such as the greatest value for any given user. In that case you would want

在实际情况中，您更可能希望得到一个可预测的值，例如任何给定用户的最大值。那样的话，你会想要的

update table_b as b
inner join (
   select user, max(value) from table_a
   group by user ) as a_max on a.user = b.user
set b.value = a_max.value

#3

Your question is unclear about what to do about any values that are already in b. If you use a left join, then these will explicitly be set to NULL:

您的问题是不清楚如何处理b中的任何值。如果您使用左连接，那么这些值将显式设置为NULL:

update table_b b left join
       table_a a
       on a.User = b.User
    set b.value = a.value;

If you want to keep the existing values for non-matches, then use inner join.

如果您希望保留不匹配的现有值，则使用内连接。

Note that this might be inefficient, but should be ok if an index exists on a(user).

请注意，这可能是低效的，但是如果索引存在于(用户)上，应该是可以的。

If you had very few users in a and lots and lots of duplicates, then you might want to aggregate a before doing the join.

如果您的用户很少，并且有很多重复，那么您可能希望在加入之前聚合a。

#1