Multi-part identifier could not be bound- SQL update queries based off of matching variables in two tables - URGENT

I'm trying to update a field in table 1 (pricing.mod_type)to = a field in table 2 (archive.pendingmod),  but I only want to select the most recent of [archive.pendingmod] based off of a incrementing key field. I am matching the two tables based on a master number, which appears multiple times in the archive (one record per different pendingmod, but i just want the most recent, hence selecting the max of the key). I am also matching this where archive.contractlistprice=pricing.contractlistprice and only updating those where pricing.contract='xxxxxxxxx' (obscured for company privacy)

Here is my code:

update pricing
set [pricing].[mod_type]=[archive].[pendingmod]
      select max([archive].[key])
      from [archive]
      where [pricing].[immixmasterno]=[archive].[immixmasterno]
            and [pricing].[contractlistprice]=[archive].[contractlistprice]
            and [pricing].[contract]='XXXXXXj'

And here are my errors:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "archive.pendingmod" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "pricing.immixmasterno" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "pricing.contractlistprice" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "pricing.contract" could not be bound.


Start Free Trial