Update or Append query

[ChuckW]

Hi,
I have a transactional table called SBMSALE2008 that has fields called HPKey
(the primary key), PartNumber and another called HPProductLineCode (along
with several other fields). The ProdLineCode field is blank. There is
another table called Products that has two fields (ProductNumber and
ProductLineCode). The PartNumber field is the same as the ProductNumber
except that 4-5 characters are added to it which means it is not a direct
match to Products.ProductNumber. However, If I create a query that has
ProdNumber:Left([PartNumber],10), then ProdNumber is a match with
Products.ProductNumber. What I want to do is to create an update or append
query (I am not sure which to use), that joins the newly created ProdNumber
to Products.ProductNumber and then populates the null values in
SBMSALE2008.HPProductLineCode with values from Products.ProductLineCode. How
do I do this?

Thanks,

 

[Michel Walsh]

With Jet, you can use an Update-existing-Append-new.


Assume a price list of items to be updated with newPrices:

UPDATE priceLists RIGHT JOIN newPrices
ON priceLists.itemID = newPrices.itemID
SET priceList.itemID = newPrices.itemID,
priceList.UnitPrice = newPrices.UnitPrice



will update the unit prices of existing items AND will append new items,
not originally in the updated list.


That won't work in MS SQL Server (2005 or before).



Hoping it may help,
Vanderghast, Access MVP