从表b(条件)更新表a

Evening all,

晚上,

Actually, it's night. About 11pm. My brain is shutting down and I need a bit of help so I can finish and go home :)

事实上,它的夜晚。大约11点。我的大脑停止运转了，我需要一点帮助，这样我就可以回家了。

I have two tables - table a and table b. I need to update a field in table a with the value from a field in table b when two other fields match. The tables don't have a unique id for each record :(

我有两个表——表a和表b。我需要在表a中更新一个字段，当另外两个字段匹配时，表b中的字段值。对于每个记录，表都没有惟一的id:(

Basically, I want to do this:

基本上，我想这样做:

update a
set importantField = 
(select b.importantfield
from b
where a.matchfield = b.matchfield
and a.matchfield2 = b.matchfield2
)
where a.matchfield = b.matchfield
and a.matchfield2 = b.matchfield2

Or at least... I think that's what I want to do...

或者至少……我想这就是我想做的…

Can someone help me out, please?

有人能帮我吗?

2 个解决方案

#1

You can do this via a join in the update:

您可以通过加入更新:

Update a
Set a.importantField = b.importantField
From a Join b 
  On a.matchfield = b.matchfield
  And a.matchfield2 = b.matchfield2

#2

Use:

使用:

UPDATE TABLE_A
   SET importantField = (SELECT b.importantfield
                           FROM TABLE_B b
                          WHERE b.matchfield = matchfield
                            AND b.matchfield2 = matchfield2)

SQL Server doesn't support table aliases on the table being updated, but the above is a correlated query - those fields without the table alias b attached will serve values from TABLE_A because it doesn't have an alias.

SQL Server不支持正在更新的表上的表别名，但上面是一个相关的查询——没有附加表别名b的字段将从TABLE_A提供值，因为它没有别名。

The only issue beyond that is if there are multiple b.importantfield values for records with the matching records to TABLE_A. Use:

唯一的问题是如果存在多个b。输入字段值，以记录与表_a匹配的记录。使用:

UPDATE TABLE_A
   SET importantField = (SELECT TOP 1 
                                b.importantfield
                           FROM TABLE_B b
                          WHERE b.matchfield = matchfield
                            AND b.matchfield2 = matchfield2)

..but you should use an ORDER BY as well or you'll get any random b.importantfield value.

. .但是你也应该使用顺序，否则你会得到任意的b。importantfield价值。

#1