Update products table based on sku prefix

Question

I have a products table, that has a manufacturerID that is currently -1 for all the products.

I have a manufacturer table that has a SKU prefix.

So if a MFG sku prefix is: ABC

Then the products table will have products like ABC123, ABC3434.

So I need a query to update the products table, to set the manufacturerID based on the skuPrefix in the Manufacturer table.

is this possible?

Answer 1

UPDATE tblProducts
SET P.manufacturerId = M.manufacturerId
FROM tblProducts P
JOIN tblManufacturers M ON LEFT(P.Sku, 3) = M.SkuPrefix
-- above, replace 3 with whatever the prefix length is
--WHERE  possibly some where condition

The above should do the trick. A few considerations however:

• in the case of a very big product table, it may be preferable to perform these updates in small[er] batches, i.e. by introducing some where condition (depending on the recovery model, this may avoid clobbering the SQL log too much)
• the length of the prefix needs to be defined, of course, I used 3 for illustration purposes, one may need 5 or 8 ?
• if somehow the prefix length is variable, one may be able to use
  ... (ON CHARINDEX(P.Sku, M.SkuPrefix) = 1)
  as the join condition.

Answer 2

UPDATE tblProducts 
SET manufacturerId = M.manufacturerId 
FROM tblProducts P 
JOIN tblManufacturers M ON M.Sku + '%' LIKE P.sku

Should do it