Update table a from table b where (conditions)

问题:

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 :)

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 :(

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?

回答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.

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

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.