0

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.

  • The server is 2008r2 and SQL Server 2017 – ChrissB May 2 '18 at 22:26
2

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

  • 1
    @ChrissB You were so close! Please still update your question with data so that this might be useful to others in the future :) This site might help with formatting sample data. – LowlyDBA May 2 '18 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.