开发者

Update products table based on sku prefix

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 t开发者_JS百科he Manufacturer table.

is this possible?


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.


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

Should do it

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜