Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 5 of 5

Thread: Changing Most SKU's

  1. #1
    DotNetDevelopments is offline Senior Member
    Join Date
    Jul 2008
    Location
    Harlow / Essex / UK
    Posts
    619

    Exclamation Changing Most SKU's

    The way we have done our SKU's is to have the manufactures name then the product code.

    However now we need to change this to just the product code and take out the manufacturers name. We do not mind doing this on simple products as it is quick to do. However we have around 700 items that are variables. This means the top variable has the manufactures name and the variants have the part number.

    Can someone help us make a SQL query that will check if the product has a variable with a SKU if so to delete the SKU of the master product.

    Our simple products have variables however the variables SKU is blank (null) if it is a simple product. So by using that logic we should be able to look through the database and work out what is not a simple product and delete the SKU of the master product.

    Again, any help on this would be great.
    Thanks.
    =====
    Version (Code/DB): AspDotNetStorefront MSx 9.1.0.1/9.1.0.0
    Execution Mode: 64 Bit
    Dot Net Developments - E-commerce By Experience

  2. #2
    BFG 9000 is offline Senior Member
    Join Date
    Oct 2006
    Location
    South UK
    Posts
    882

    Default

    It will look something like this :-

    UPDATE dbo.Product SET SKU = '' where ProductID IN (Select ProductID FROM dbo.ProductVariant WHERE SKUSuffix != '');


    N.B. I haven't used any double quotes above - they're 2 single quotes next to each other.


    N.B. - Always backup before doing anything like this


    TTFN

    BFG

  3. #3
    DotNetDevelopments is offline Senior Member
    Join Date
    Jul 2008
    Location
    Harlow / Essex / UK
    Posts
    619

    Default

    Just tested this locally, worked perfectly! thanks for that!
    =====
    Version (Code/DB): AspDotNetStorefront MSx 9.1.0.1/9.1.0.0
    Execution Mode: 64 Bit
    Dot Net Developments - E-commerce By Experience

  4. #4
    jamotion is offline Senior Member
    Join Date
    Jul 2007
    Posts
    215

    Default

    1) Im updating my products and some skus are updated. If I update the old skus with the new, will this impact the products or break anything?
    2) Will it create dups?

  5. #5
    AspDotNetStorefront Staff - Scott's Avatar
    AspDotNetStorefront Staff - Scott is offline Administrator
    Join Date
    Mar 2007
    Location
    Ashland, OR
    Posts
    2,390

    Default

    How are you doing the update? If you're using the Excel import (and are on 8.x), as long as the product name is the same, the old SKU will just be overwritten. Likewise with WSI.

    If you're doing something directly in the DB, talk to whoever wrote the query you're using.

    As for 'breaking anything,' there are really only a few functions that rely on SKU - things like UseSKUForProductDescriptionName, UseSKUForProductImageName, etc. They're usually not used, but if you're using any of those you'll have to make sure you make the necessary adjustments.