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
精彩评论