JOIN and concatenate between tables and UPDATE The server is SQL Server 2008 R2 and SQL Server 2017. Probably a simple answer to this one, but it has me a bit stumped.
Ok so I have two tables, one has basic vendor inventory information and the other is a vendor code lookup to match it to our product database.  The vendor inventory is updated nightly from a downloaded XML file so I don't have control over how it comes in sadly.

What I'm needing to do is JOIN the two tables and UPDATE one of the columns with the concatenation of two of the columns from two different tables.

I have the concatenation done:

SELECT concat(SFXVencode, partnumber) as Pcode
  FROM KeyInventory JOIN Keybrands
    ON keybrands.vencode = keyinventory.vencode 


But what I need is the ability to save this into a column in the keyinventory table.

So the concatenated productcode (what matches to our product database) needs to be saved in a column.

This is how far I've gotten, but it's not working currently.

UPDATE KeyInventory
SET KeyInventory.Productcode = (

SELECT concat(SFXVencode, partnumber) as Pcode
  FROM KeyInventory JOIN Keybrands
    ON keybrands.vencode = keyinventory.vencode     
        )


I'm needing the KeyInventory.Productcode to be updated to the Pcode

I hope this all makes sense and I would be forever grateful for a fix for this.
KeyBrands


    
        
            SFXVenCode
            VenCode
        
        
            ACC
            A51
        
    


KeyInventory


    
        
            PartNumber
            VenCode
            ProductCode
        
        
            3102
            A51
            (what I need)
        
    


So the Concatenated would be ACC3102 and I need it saved into the KeyInventory table.

Sorry, cannot use html to format the table data.. argh.
Does this work?

UPDATE KeyInventory 
SET KeyInventory.Productcode = concat(SFXVencode, partnumber)
FROM KeyInventory 
   INNER JOIN Keybrands ON keybrands.vencode = keyinventory.vencode


Might need a little tweaking once you post your DDL...